DATEFORM Teradata

Teradata has traditionally been Y2K compliant. In reality, it is compliant to the years beyond 3000. However, the default display format using YY/MM/DD is not ANSI compliant.

In Teradata, release V2R3 allows a choice of whether to display the date in the original display format (YY/MM/DD) or the newer ANSI format (YYYY-MM-DD). When installed, Teradata defaults at the system level to the original format, called INTEGERDATE. However, this system default DATEFORM may be over-ridden by updating the DBS Control record.

The DATEFORM:

  • Controls default display of selected dates
  • Controls expected format for import and export of dates as character strings (‘YY/MM/DD’ or ‘YYYY-MM-DD’) in the load utilities
  • Can be over-ridden by USER or within a Session at any time.

System Level Definition

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

User Level Definition

CREATE USER username .......



DATEFORM={INTEGERDATE | ANSIDATE} ;

Session Level Declaration

In addition to setting the system default in the control record, a user can request the format for their individual session. The syntax is:

SET SESSION DATEFORM = {ANSIDATE | INTEGERDATE} ;

In the above settings, the "|" is used to represent an OR condition. The setting can be ANSIDATE or INTEGERDATE. Regardless of the DATEFORM being used, ANSIDATE or INTEGERDATE, these define load and display characteristics only. Remember, the date is always stored on disk in the YYYMMDD format, but the DATEFORM allows you to select the format for display.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics