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 1 lists the supported date/time built-in functions. Table 2 lists the supported date/time patterns and Table 3 lists the supported time-only 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. |
DAYSTOMICROSECS | Converts a number of days to a number of microseconds. |
DAYSTOSECS | Converts a number of days to a number of seconds. |
JULIANTOSMF | Converts a date from Julian format to SMF format. |
MAXDATE | Returns the latest date/time value for a specified date/time pattern. |
MICROSECS | Returns the number of microseconds corresponding to a date/time pattern string, or the number of microseconds for today's date. |
MICROSECSTODATE | Converts a number of microseconds to a date/time pattern string. |
MICROSECSTODAYS | Converts a number of microseconds to a number of days. |
MINDATE | Returns the earliest date/time value for a specified date/time pattern. |
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. |
SMFTOJULIAN | Converts a date from SMF format to Julian format. |
STCKETODATE | Converts a STCKE value to a date-time pattern string. |
TIME | Returns the current time in the pattern HHMISS999. |
TIMESTAMP | Returns the current time in the pattern YYYY-MM-DD-HH.MI.SS.999999. |
UTCDATETIME | Returns the current Coordinated Universal Time (UTC) in the pattern YYYYMMDDHHMISS999. |
UTCMICROSECS | Returns the number of microseconds corresponding to the current UTC time. |
UTCSECS | Returns the current Coordinated Universal Time (UTC) in the Lilian format in seconds. |
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. |
Four-digit years | Two-digit years | |
---|---|---|
Year first |
YYYYMMDD |
YYMMDD |
Month first |
MMDDYYYY |
MMDDYY |
Day first |
DDMMYYYY |
DDMMYY |
Db2 formats |
YYYY-MM-DD |
YY-MM-DD |
without zeros |
ZY-ZM-ZD |
Basic format | Extended format |
---|---|
HHMISS | HH:MI:SS |
HHMI | HH:MI |
HH |
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.
On input, the date value for the patterns "without zeros" may be less than 8 characters, for example, the date 20 Jan 2008 may be specified as 8-1-20 to match the pattern "ZY-ZM-ZD". On output, the string produced for one of these patterns will always be 8 characters with any suppressed zeros compensated by trailing blanks.