Teradata Date Handling with Sample Queries

Teradata Date Handling with Sample Queries

Teradata Date Handling:

For Developer, handling the date is a very tedious task. the date format must be checked before inserting the date in the table.

there are many ways to handle it.

Sample table:

CREATE VOLATILE TABLE SAMPLE_DATE, NO FALLBACK
(
INPUTDATE DATE FORMAT ‘MMMDDYYYY’
)
ON COMMIT PRESERVE ROWS;

 

Explicitly :

If you are not aware of the date format in the table definition then you could define it while inserting records explicitly.

INSERT INTO SAMPLE_DATE VALUES (’31FEB2016′ (DATE,FORMAT‘DDMMMYYYY’));

SELECT * FROM SAMPLE_DATE;

Output :

2010-01-31

Default format:

If you are aware of the format then you could insert the row directly.

INSERT INTO SAMPLE_DATE VALUES (‘FEB122016’);

SELECT * FROM SAMPLE_DATE;

Output:

2016-02-12

Numeric or integer format

There is another way to insert date into the table. the Teradata handles the date internally in the numeric format and you could use the same way to set the date.

INSERT INTO XYZ VALUES (1161106);

SELECT * FROM XYZ;

Output :

2016-11-06

ANSI Format

Teradata also supports the ANSI standard.

INSERT INTO xyz VALUES ( DATE ‘2016-12-12’);

SELECT * FROM XYZ;

Output :

2016-12-12

 

TIME  and  CURRENT_TIME:

Teradata time and current_time functions return time but perform differently logically.

TIME CURRENT_TIME
The time function returns time in float format It returns time with Zone
Time is a Teradata extension Ansi Standard
Example 94725 09:42:17+00:00

Example Query:

select time ,current_time ;

Output :  94217.6      09:42:17+00:00

Screenshot :

Teradata time
Teradata time

Teradata time zone

Teradata allows to set up the time zone for the session explicitly. the time zone is assign by the database administrator for the users while setting up the server.  the time zone describes the relative time difference with GMT.

For india  time difference w.r.t GMT is +05:30

 

Setting up time zone in Teradata:

Default time zone: it is set up by database administrator as per the server location.

User Creation: Time zone for the user can be defined while configuring new user

Explicitly for session: the user can change the time zone for the session manually

SET TIME ZONE LOCAL /* As per default setting on server*/

SET TIME ZONE USER /* time zone defined for the user */

SET TIME ZONE INTERVAL ’05:30′ HOUR TO MINUTE /* defining the time zone explicitly for the session*/

Inserting  record into timestamp column with time zone:

Creating table SAMPLE_TEST with  column name tstamp_col:

CREATE VOLATILE TABLE SAMPLE_TEST (tstamp_col TIMESTAMP WITH TIME ZONE

) ON COMMIT PRESERVE ROW

Inserting records into SAMPLE_TEST table:

INSERT INTO SAMPLE_TEST VALUES (TIMESTAMP '2016-02-02 10:36:56.132435');

select * from SAMPLE_TEST

Output :2016-02-02 10:36:56.132435+05:30

**  By default the time zone for the session is considered while inserting the records

Extracting Time zone hours  and minutes from timestamp

SELECT current_timestamp(0),EXTRACt(TIMEZONE_HOUR FROM current_timestamp(6) )  as HOURS ,

EXTRACt(TIMEZONE_MINUTE FROM current_timestamp(6)) as MINUTES;

Output :

current_timestamp(0) HOURS MINUTES
2016-10-13 10:37:34+05:30 5 30

Share this post

Leave a Reply