All Forums Tools
varun2102 37 posts Joined 12/06
07 Jan 2008
AM/PM in TIMESTAMP

Hi,I am trying to load mainframe data into a teradata table. I want to load a timestamp field in TD (Say, Birth_date) with a 12 hour time in the following way:selcast( TRIM(CAST(SUBSTR('20041212143245',1,8) AS DATE FORMAT 'YYYYMMDD')) || -- ' ' || TRIM(CAST(SUBSTR('20041212143245',9,2) AS INTEGER)-12) || -- ':' || TRIM(SUBSTR('20041212143245',11,2)) || --':' || TRIM(SUBSTR('20041212143245',13,2))as timestamp format 'yyyymmddhhmiss') || ' PM'will this be accepted? considering there is a PM in there? If not, then cant TIMESTAMP in Teradata not have time in the 12 hour format at all (including the AM or PM) ???

Fred 1096 posts Joined 08/04
07 Jan 2008

It's not clear from your example what you are trying to do, but perhaps one of these will help:Convert text input to Teradata TIMESTAMP field:SELECT CAST('20041212143245' AS TIMESTAMP(0) FORMAT 'yyyymmddhhmiss');SELECT CAST('20041212 02:32:45 PM' AS TIMESTAMP(0) FORMAT 'yyyymmddbhh:mi:ssbt');Output Teradata TIMESTAMP field in 12-hour format:SELECT CAST(CAST(TIMESTAMP'2004-12-12 14:32:45' AS FORMAT 'yyyymmddbhh:mi:ssbt') AS VARCHAR(31));

bhartiya007 29 posts Joined 03/14
24 Sep 2015

Hi All,
I am trying to do the following:
sel           cast ('20141121 083906 PM'                   as TIMESTAMP(6) format  'YYYYMMDDBHHMISSBT').
This is working fine for non zero Hours. However, for hour like 00 the above query is throwing invalid timestamp error.
Any idea why?
 

@Amit

dnoeth 4628 posts Joined 11/04
24 Sep 2015

00:xx AM doesn't exist, it's 12:xx AM.
https://en.wikipedia.org/wiki/12-hour_clock
That's why there's 24 hour format :-)

Dieter

bhartiya007 29 posts Joined 03/14
27 Sep 2015

Ohh..yes..thanks Dnoeth :-).
My data is faulty.
 
Thanks a Lot!!
 
Regards,
Amit

@Amit

You must sign in to leave a comment.