Teradata DATE-TIME Part 1 - Date Standard formats and DATEFORM

Traditional Teradata date format : 'YYYY/MM/DD'

ANSI date format : 'YYYY-MM-DD'

This formats are selectable with SET SESSION DATEFORM option.

What does the DATEFORM change impact?

Option we choose impacts the we display , import and export the date fields.

Note that irrespective which format we choose the date is always stored as integers in the same way - 'yyymmdd' where yyy is an offset on number of years after 1900 year.

Thus the way date is stored internally does not change.

What are the 3 places to change Default formats?

3 places to change date default format:

1. DBSCONTROL record --> This will become system default.
2. While defining the user --> This will become default for the user.
3. DATEFORM setting --> This will change the default for the session.

Example

MODIFY GENERAL 14=0 /* INTEGERDATE (YY/MM/DD)*/
MODIFY GENERAL 14=1 /* ANSIDATE (YYYY-MM-DD)*/

Example

CREATE USER ped........
       DATEFORM=ANSIDATE
               =INTEGERDATE

Example

SET SESSION DATEFORM=ANSIDATE
                    =INTEGERDATE