AM/PM in TIMESTAMP

Tools
Enthusiast

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:

sel
cast
(
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) ???
4 REPLIES
Teradata Employee

Re: AM/PM in TIMESTAMP

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));
Enthusiast

Re: AM/PM in TIMESTAMP

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?

Junior Contributor

Re: AM/PM in TIMESTAMP

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 :-)

Enthusiast

Re: AM/PM in TIMESTAMP

Ohh..yes..thanks Dnoeth :-).

My data is faulty.

Thanks a Lot!!

Regards,

Amit