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

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

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