Teradata DATE-TIME part 2 - DATEFORM

SET SESSION DATEFORM command:

We can switch between date formats by making use of the command SET SESSION DATEFORM.

This command can be executed at any point of time in the session.

Example:

select date;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

      Date

----------

2013/03/31   --> by default it’s the teradata format

 BTEQ -- Enter your SQL request or BTEQ command:

SET SESSION DATEFORM=ANSIDATE; --> To change it to ANSI format

SET SESSION DATEFORM=ANSIDATE;

 *** Set SESSION accepted.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

select date;

select date;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

Current Date

------------

  2013-03-31  --> This is the date output in ANSI

 BTEQ -- Enter your SQL request or BTEQ command:

SET SESSION DATEFORM=INTEGERDATE; --> To convert to teradata format we use the keyword INTEGERDATE

SET SESSION DATEFORM=INTEGERDATE;

 *** Set SESSION accepted.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

sel date;

sel date;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

      Date

----------

2013/03/31

 BTEQ -- Enter your SQL request or BTEQ command:

Note that keywords to be used are ANSIDATE and INTEGERDATE, and not ANSI /BTET

Importing Data from file:

Suppose we have a file containing date as follows:

more inputfile

2013/11/23

Following is the code we put in bteq to load data from file to the table temp1

.IMPORT FILE=inputfile;

USING newdate (CHAR(10))

INSERT INTO temp1 VALUES (:newdate);