to_timestamp conversion behavior

Teradata Applications
Scout

to_timestamp conversion behavior

select to_timestamp('Oct 13 2016 12:44AM', 'Mon DD YYYY HH:MIAM') x1;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

x1
--------------------------
2016-10-13 12:44:00.000000

Teradata BTEQ 15.10.01.01 for WIN32.

select to_timestamp('Oct 13 2016 12:44PM', 'Mon DD YYYY HH:MIAM') x2;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

x2
--------------------------
2016-10-13 12:44:00.000000

 

Both queries return the same value. Seems to ignore the AM/PM conversion. Is this correct? or Am I doing something wrong?  This is on TD15.10.  Thanks

1 REPLY
Junior Supporter

Re: to_timestamp conversion behavior

Hi.

 

You must use 'T' format:

 

BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(CAST ('2016-10-14 12:05:05AM' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDbhh:mi:ssT') AS FORMAT 'YYYY-MM-DDbhh:mi:ss');


*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

'2016-10-14 12:05:05AM'
-----------------------
2016-10-14 00:05:05

BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(CAST ('2016-10-14 12:05:05PM' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDbhh:mi:ssT') AS FORMAT 'YYYY-MM-DDbhh:mi:ss');


*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

'2016-10-14 12:05:05PM'
-----------------------
2016-10-14 12:05:05

 

Cheers.

 

Carlos.