These 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. Table 49 lists the supported date/time built-in functions. Table 50 lists the supported date/time patterns.
The time zone and accuracy for these functions are system dependent.
The Lilian format counts days that have elapsed since October 14, 1582; day one is Friday, October 15, 1582. For example, 16 May 1988 is 148138 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.
| Function | Description |
|---|---|
| DATE | Returns the current date in the pattern YYMMDD |
| DATETIME | Returns the current date and time in the user-specified pattern or in the default pattern YYYYMMDDHHMISS999 |
| DAYS | Returns the number of days corresponding to a date/time pattern string, or the number of days for today's date |
| DAYSTODATE | Converts a number of days to a date/time pattern string |
| DAYSTOSECS | Converts a number of days to a number of seconds |
| REPATTERN | Takes a value holding a date in one pattern and returns that value converted to a date in a second pattern |
| SECS | Returns the number of seconds corresponding to a date/time pattern string, or the number of seconds for today's date |
| SECSTODATE | Converts a number of seconds to a date/time pattern string |
| SECSTODAYS | Converts a number of seconds to a number of days |
| TIME | Returns the current time in the pattern HHMISS999 |
| VALIDDATE | Indicates if a string holds a valid date |
| WEEKDAY | Returns the day of the week corresponding to the current day or specified DAYS value |
| Y4DATE | Takes a date value with the pattern 'YYMMDD' and returns the date value with the two-digit year widened to a four-digit year |
| Y4JULIAN | Takes a date value with the pattern 'YYDDD' and returns the date value with the two-digit year widened to a four-digit year |
| Y4YEAR | Takes a date value with the pattern 'YY' and returns the date value with the two-digit year widened to a four-digit year |
Table 50 uses the following formats:
The only supported pattern using any of HH, MI, SS or 999 is the pattern 'YYYYMMDDHHMISS999'.
|
|
Four-digit years |
Two-digit years |
|---|---|---|
|
Year first |
YYYYMMDDHHMISS999 YYYYMMDD YYYYMMMDD YYYYMmmDD YYYYDDD YYYYMM YYYYMMM YYYYMmm YYYY |
YYMMDD YYMMMDD YYMmmDD YYDDD YYMM YYMMM YYMmm YY |
|
Month first |
MMDDYYYY MMMDDYYYY MmmDDYYYY MMYYYY MMMYYYY MmmYYYY |
MMDDYY MMMDDYY MmmDDYY MMYY MMMYY MmmYY |
|
Day first |
DDMMYYYY DDMMMYYYY DDMmmYYYY DDDYYYY |
DDMMYY DDMMMYY DDMmmYY DDDYY |
|
DB2 formats |
YYYY-MM-DD MM/DD/YYYY DD.MM.YYYY |
YY-MM-DD MM/DD/YY DD.MM.YY |
When the day is omitted from a pattern, it is assumed to have the value 1. If the month and day are both omitted, they are also assumed to have the value 1.
When using MMM, the date must be written in three uppercase letters; when using Mmm, the date must be written with the first letter in uppercase, and the letters following in lowercase.