Casting a String Date to TimeStamp datatype

When we are dealing with string you need to cast the date string to date and afterwards the date to a timestamp.

Example :

select cast(cast('2011-07-11' as date) as timestamp(0));

Note that above assumes  'YYYY-MM-DD' date format.

If the date string has a different format then try the following

select cast(('2011/07/11' (date, format'YYYY/DD/MM')) as timestamp(0))




where you can explicit specify your date format...

Additional Examples:

     Insert into dt
     Select cast(cast('2011/07/11' as date ) as timestamp(6) );

     Insert into dt
     Select cast(cast('2011/07/11' as date format 'yyyy/mm/dd') as timestamp(6));

==============

How to combine date and time column to form a timestamp field.







The most efficient way to combine a date and a time is to cast the date to a timestamp and add the time. But you can’t simply add a time, because TIME is a point in time and not a duration. So you have to transform it into an interval:




cast(Date_column as TimeStamp(6))
+ ((Time_column - time '00:00:00') hour to second(6)) 
 
Example:
  SELECT CAST(DATE'1999-12-30' AS TIMESTAMP(6))
+ ((TIME '12:12:21' - TIME '00:00:00') HOUR TO SECOND(6))
gives:
 (1999-12-30+(12:12:21 - 00:00:00) HOUR TO SECOND)
 1999-12-30 12:12:21.000000
 
We can also confirm the data type of the value returned by using TYPE

  SELECT TYPE(CAST(DATE'1999-12-30' AS TIMESTAMP(6))
+ ((TIME '12:12:21' - TIME '00:00:00') HOUR TO SECOND(6)))
gives
 Type((1999-12-30+(12:12:21 - 00:00:00) HOUR TO SECOND))
 TIMESTAMP(6)

Main point to be noted here is that we cant simply add TIME.
First we need to convert it to interval and then only add to timestamp.
(courtesy-Teradata forum)


 

Few more Timestamp calculations Examples as below:

SELECT TIMESTAMP1 FROM VT_DATE_TIME; — 2014-01-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' YEAR FROM VT_DATE_TIME; — 2012-01-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' MONTH FROM VT_DATE_TIME; — 2013-11-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02-02' YEAR TO MONTH FROM VT_DATE_TIME; –2011-11-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' DAY FROM VT_DATE_TIME; — 2014-01-05 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' HOUR FROM VT_DATE_TIME; — 2014-01-07 02:21:17

SELECT TIMESTAMP1 - INTERVAL '02' MINUTE FROM VT_DATE_TIME; — 2014-01-07 04:19:17

SELECT TIMESTAMP1 - INTERVAL '02' SECOND FROM VT_DATE_TIME; — 2014-01-07 04:21:15

SELECT TIMESTAMP1 - INTERVAL '02 02' DAY TO HOUR FROM VT_DATE_TIME; — 2014-01-05 02:21:17

SELECT TIMESTAMP1 - INTERVAL '02 02:02' DAY TO MINUTE FROM VT_DATE_TIME; — 2014-01-05 02:19:17

SELECT TIMESTAMP1 - INTERVAL '02 02:02:02' DAY TO SECOND FROM VT_DATE_TIME; — 2014-01-05 02:19:15

SELECT TIMESTAMP1 - INTERVAL '02:02' HOUR TO MINUTE FROM VT_DATE_TIME; — 2014-01-07 02:19:17

SELECT TIMESTAMP1 - INTERVAL '02:02:02' HOUR TO SECOND FROM VT_DATE_TIME; — 2014-01-07 02:19:15

SELECT TIMESTAMP1 - INTERVAL '02:02' MINUTE TO SECOND FROM VT_DATE_TIME; — 2014-01-07 04:19:15

Subtract two timestamp columns:
SELECT (CURRENT_TIMESTAMP(0) - TIMESTAMP1) HOUR TO SECOND(0) FROM VT_DATE_TIME; — 0:12:52