Some tips which might be useful while working on date and timestamp. (queries run and verified on V2R6)
Tip 1: Create Timestamp from Date and Time
SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0));
SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));
Tip 2: Convert timestamp field in Teradata into format 'MMM-YYYY'
select CURRENT_TIMESTAMP (FORMAT 'MMM-YYYY');
Current TimeStamp(6)
-------------------
Mar-2010
Tip 3: Inserting current_timestamp value into timestamp(0) column
Create table stats(record_time TIMESTAMP(0));
Whenever we try to populate the current_timestamp value into this column using
insert into stats(record_time) select current_timestamp ; we get following error.
7454: Date Time field overflow.
To avoid this, we have two solutions
Solutions:
1. Data type for CURRENT_TIMESTAMP is TIMESTAMP(6) and you are trying to insert a
TIMESTAMP(6) data to a TIMESTAMP(0) and that's why you are getting this error.
insert into stats(record_time)
Sel cast(CURRENT_DATE as TimeStamp(0))
+
((CURRENT_TIME - time '00:00:00') hour to second(0)) ;
2. insert into stats(record_time) select CURRENT_TIMESTAMP(0);
Tip 4: Add minutes to timestamp
How to add 10 minutes to current timestamp in TD?
SEL CURRENT_TIMESTAMP + INTERVAL '10' MINUTE.
The following example is taken straight from Teradata Functions and Operators reference manual.
Example 1: Subtract two TIMESTAMP Types
Consider the following table:
CREATE TABLE BillDateTime
(start_time TIMESTAMP(0),end_time TIMESTAMP(0));
Determine the difference, specifying an Interval unit of DAY TO SECOND for the result:
SELECT (end_time - start_time) DAY(4) TO SECOND FROM BillDateTime;
The DAY(4) specifies four digits of precision, and allows for a maximum of 9999 days, or
Approximately 27 years.
The result is an Interval that looks like this:
5 16:49:20.340000
Tip 5: Convert varchar to timestamp
have a column in source strdate1 as VARCHAR(23) and the value being '20100310181010'.
SELECT CAST ('20100310181010' AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS');
Tip 6: Display Timestamp with AM /PM format
SELECT CAST(TIMESTAMP'2010-03-12 14:32:45' AS FORMAT 'yyyymmddbhh:mi:ssbt') (char(20));
Tip 7: Display timestamp in 22 Mar 2010 00:26:54.840 format
select cast(current_TIMESTAMP(3) as timestamp(3) FORMAT 'ddbmmmbyyyybhh:mi:ss.s(3)')(char(25));
Tip 8: Subtract two TIMESTAMP Types
Consider the following table:
CREATE TABLE BillDateTime (start_time TIMESTAMP(0),end_time TIMESTAMP(0) );
Determine the difference, specifying an Interval unit of DAY TO SECOND for the result:
SELECT (end_time - start_time) DAY(4) TO SECOND FROM BillDateTime;
The DAY(4) specifies four digits of precision, and allows for a maximum of 9999 days, or
Approximately 27 years.
The result is an Interval that looks like this:
5 16:49:20.340000
Tip 9: Extract function on DATE & TIME
Extracting From Current Date(March 20,2010)
· Query Result
SELECT DATE; 10/03/20 (Default format)
SELECT EXTRACT(YEAR FROM DATE); 2010
SELECT EXTRACT(MONTH FROM DATE); 03
SELECT EXTRACT(DAY FROM DATE); 20
SELECT EXTRACT(YEAR FROM DATE + 365); 2011
SELECT EXTRACT(MONTH FROM DATE + 30); 04
SELECT EXTRACT(DAY FROM DATE + 12); 01
Extracting From Current Time ( 2:42 PM )
Query Result
SELECT TIME; 14:42:32 (Default format)
SELECT EXTRACT(HOUR FROM TIME); 14
SELECT EXTRACT(MINUTE FROM TIME); 42
Hi,
ReplyDeleteI am using following query to extract Date from the timestamp field.
select CAST(load_time AS DATE format 'yyyymmdd' ) FROM TABLE
still I am getting output as follows.
load_time
29/03/2012
29/03/2012
29/03/2012
29/03/2012
29/03/2012
I want to get it in the format 'YYY-MM-DD' but irrespective of the format specified in th equery, it is showing output in the same format i.e. 'DD/MM/YYYY'
Hi,
ReplyDeleteThe problem is because you are running it in sql assistant . The same query works fine in bteq mode.
Try the same query as follows
select CAST(load_time AS DATE format 'yyyymmdd' )(char(8)) FROM TABLE
I love this site; outstanding examples and explanations! Johnathan
ReplyDeleteits really awesome
ReplyDeleteThanks for your effort in creating the examples! Info es very clear!
ReplyDeleteHelped a lot ! Thanks from Poland ! Marcin
ReplyDelete