How to handle TIMESTAMP with AM PM values coming from Source in Teradata
In Teradata, by default dates are stored in 24 hours format. i.e 2:00 pm in Teradata would look like 14:00. However we may come across the situation in which Timestamp values coming from SOURCE may have AM PM at the end of the value, like 2013-04-25 02:25:30 PM. Now if the column in Teradata is defined as TIMESTAMP(0) , the value stored in the table would be 2013-04-25 02:25:30 which is actually wrong. The correct value should be 2013-04-25 14:25:30. So how to handle such values? To store time properly in Teradata , we need to specify the proper format of the Timestamp column which in this case would be YYYY-MM-DDBHH:MI:SSBT . Here T at the end will take care of AM PM value from the SOURCE. So while inserting such Timestamp values in Teradata, just CAST the values to proper format of TIMESTAMP as required.
Try executing below two queries:
SEL CAST(‘2013-04-25 02:26:25 PM’ AS TIMESTAMP(0) FORMAT ‘Y4-MM-DDBHH:MI:SSBT’);
Output: 2013-04-25 14:26:25 [right]
SEL CAST(‘2013-04-25 02:26:25 PM’ AS TIMESTAMP(0));
Output: 2013-04-25 02:26:25