Character Strings That Omit Day, Month, or Year | Teradata Vantage - Character Strings That Omit Day, Month, or Year - Advanced SQL Engine - Teradata Database

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.

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