Character Strings That Omit Day, Month, or Year

Teradata Vantage™ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

If the character string and the format for a character-to-DATE conversion omits the day, month, or year, the system uses default values for the target DATE value.

IF the character string omits the … THEN the system uses the …
day value of 1 (the first day of the month).
month value of 1 (the month of January).
year current year (at the current session time zone).

Consider the following table:

CREATE TABLE date_log
   (id INTEGER
   ,start_date DATE
   ,end_date DATE
   ,log_date DATE);

The following INSERT statement converts three character strings to DATE values. The first character string omits the day, the second character string omits the month, and the third character string omits the year. Assume the current year is 1992.

INSERT date_log
   (1001
   ,CAST ('January 1992' AS DATE FORMAT 'MMMMBYYYY')
   ,CAST ('1992-01' AS DATE FORMAT 'YYYY-DD')
   ,CAST ('01/01' AS DATE FORMAT 'MM/DD'));

The result of the INSERT statement is as follows:

SELECT * FROM date_log;
         id  start_date  end_date  log_date
-----------  ----------  --------  --------
       1001    92/01/01  92/01/01  92/01/01