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