When we are dealing with string you need to cast the date string to date and afterwards the date to a timestamp.
Example :
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.000000We can also confirm the data type of the value returned by using TYPESELECT TYPE(CAST(DATE'1999-12-30' AS TIMESTAMP(6)) + ((TIME '12:12:21' - TIME '00:00:00') HOUR TO SECOND(6)))givesType((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
No comments:
Post a Comment