Date/time built-in functions return or manipulate date and time information in terms of days, seconds, and character date/time stamps. Some of these built-in functions allow you to specify the date/time patterns to be used.
The time zone and timing accuracy for these functions are system dependent.
Date/time patterns use these formats:
| YYYY | Four-digit year |
| YY | Two-digit year |
| ZY | Two-digit year without leading digits |
| MM | Two-digit month |
| ZM | Two-digit month without leading digits |
| MMM | Three-letter month in UPPERCASE (Ex: DEC) |
| Mmm | Three-letter month in Sentence Case (Ex: Dec) |
| DD | Two-digit day in a given month |
| ZD | Two-digit day in a given month without leading digits |
| DDD | Number of days within a given year |
| HH | Number of hours within a given day |
| MI | Number of minutes within a given hour |
| SS | Number of seconds within a given minute |
| 999 | Number of milliseconds within a given second |
| 999999 | Number of microseconds within a given second |
The only supported pattern using any of HH, MI, SS or 999 is the pattern YYYYMMDDHHMISS999.
These are the supported date/time patterns:
| Four-digit years | Two-digit years | |
|---|---|---|
| Year first |
|
|
| Month first |
|
|
| Day first |
|
|
| DB2 formats |
|
|
| Without zeros |
|
The day portion of the pattern has a value of 1 when not specified. If both the month and day portions of the pattern are not specified, they have a value of 1.
When using MMM, the date must be written in three uppercase letters. When using Mmm, the date must be written in sentence case.
On input, the date value for the "Without zeros" patterns may be less than 8 characters. For example, the date 20 Jan 2008 can be specified as 8-1-20 to match the pattern ZY-ZM-ZD. On output, the string produced for one of these patterns is always eight characters, with any suppressed zeros compensated by trailing blanks.
For YYYY-MM-DD-HH.MI.SS.999999 and YYYY-MM-DD HH:MI:SS.999999, the last three digits of the microseconds portion (999999) are always zero.
The Lilian format represents a date as the number of days or seconds from the beginning of the Gregorian calendar. This format is useful for performing calculations involving elapsed time. Lilian dating was invented in 1986 as a means to calculate the number of days or seconds between two dates that have occurred since the beginning of the Gregorian calendar. The Lilian format counts days that have elapsed since October 14, 1582, which is the start of the Gregorian calendar. Day One is Friday, October 15, 1582.
For example, 18 June 1988 is equal to 148172 Lilian days. The valid range of Lilian days is 1 to 3,074,324 (15 October 1582 to 31 December 9999).
For the number of elapsed seconds, the Lilian format counts elapsed seconds starting at 00:00:00 14 October 1582. For example, 00:00:01 on 15 October 1582 is 86,401 (24*60*60+1) Lilian seconds, and 19:01:01 16 May 1988 is 12,799,191,661 Lilian seconds. The valid range of Lilian seconds is 86,400 to 265,621,679,999.999 (23:59:59:999 31 December 9999) seconds.