Tuesday 3 January 2012

Teradata: Inseting date literals or comparing with date literals

One of the most difficult task when working with dates in teradata is to insert date literals or comparing with date literals.
Below i have shown some very basic ways to work with date literals.

Assume that we have a table with date field defined as below:

CREATE VOLATILE TABLE ABC, NO FALLBACK
 (
 INPUTDATE DATE FORMAT 'MMMDDYYYY'
 )
  ON COMMIT PRESERVE ROWS


Following are the following ways to insert date literals or strings to this table:

1) Use the same format in which column is defined in the table.
Here the date column format is MMMDDYYY.
So a  query like below will work:

  INSERT INTO ABC VALUES ('JAN122011'); /*THE FORMAT OF THE LITERAL IS SAME AS THAT DEFINED IN THE TABLE*/
  SELECT * FROM ABC;

2) Explicitly specify the format in which the date string exists.
This comes to use especially when we are loading data through mload, where date data comes in as char or varchar.

  INSERT INTO ABC VALUES ('31JAN2010' (DATE,FORMAT'DDMMMYYYY')); /* NOTE THAT HERE WE EXPLICITLY SPECIFY WHAT FORMAT THE DATE STRING IS IN*/
  SELECT * FROM ABC;

3) Using ANSI standard form
As we know the ANSI standard date format is YYYY-MM-DD.
So we can use below query to insert a row.


  INSERT INTO ABC VALUES ( DATE '1941-12-12')/* NOTE THAT WE HAVE TO WRITE DATE I ANSI FORMAT AND ALSO USE THE KEYWORD DATE IN FRONT OF IT*/
  SELECT * FROM ABC

4) Using numeric format

As we know Date is internally stored as integer. That is why the space required by both Date and integer is 4 bytes each.
So we can directly insert a numeric value as well.

INSERT INTO ABC VALUES (411207);

Date inserted will be 7th dec 1941.

All the above also stands true while comparing a date column with date string also.

*********************************************************************************









No comments:

Post a Comment