Teradata Timestamp Part 3 - Timestamp casting

Timestamp Casting:

• We can Cast a Date to Timestamp

select cast (date as timestamp(1));

Date

4/11/2013 00:00:00.0   --> The Time part of the result gets 00:00:00

• We can also cast time to timestamp

SELECT CAST(TIME '12:11:13' AS TIMESTAMP(6));

4/11/2013 12:11:13.000000 --> The date part of the result comes from the current date

• We can cast TIMESTAMP to DATE,TIME and TIMESTAMP.

SELECT CAST(TIMESTAMP '2011-02-02 12:12:43' AS DATE); --> Note that when using time/timestamp literals we should use the keyword TIME/TIMESTAMP

Casting to TIME does not work on my system

Casting as another timestamp is only reasonable if a change to the precision of seconds is needed. Note that it is not possible to cast precision to be less than the original timestamp. This results in an overflow error.

SELECT  CAST(TIMESTAMP '1999-10-01 09:30:22' AS TIMESTAMP(2)); --> this works and gives the result as 10/1/1999 09:30:22.00

SELECT  CAST(TIMESTAMP '1999-10-01 09:30:22.22331' AS TIMESTAMP(2)); --> Here we converting TIMESTAMP(5) to TIMESTAMP(2). Lowering the precision is not allowed and hence causes the query to fail with the error: SELECT Failed. 7454:  DateTime field overflow. 

Thus Casting which truncates precision is not permitted