Date/time built-in functions

These built-in functions return or manipulate date and time information in terms of days, seconds, and character date/time stamps.

Start of change 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. End of change

The time zone and accuracy for these functions are system dependent.

Lilian format: The Lilian format, named in honor of Luigi Lilio, the creator of the Gregorian calendar, 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.

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.

Table 1. Date/time built-in functions
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.
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.
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.
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.

Table 2 and Table 3 use the following formats:

YYYY: Four-digit year
YY: Two-digit year
ZY: Two-digit year with any leading zero suppressed
MM: Two-digit month
ZM: Two-digit month with any leading zero suppressed
MMM: Three-letter month (Ex: DEC)
Mmm: Three-letter month (Ex: Dec)
DD: Two-digit day within a given month
ZD: Two-digit day within a given month with any leading zero suppressed
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

Note: For the three-letter month patterns, the uppercase/lowercase characters must correspond exactly.

Start of change Three supported patterns that use any of HH, MI, SS, 999, or 999999 are YYYYMMDDHHMISS999, YYYY-MM-DD-HH.MI.SS.999999 and YYYY-MM-DD HH:MI:SS.999999. End of change

The fastest way to get today’s date and time in this pattern 'YYYY-MM-DD HH:MI:SS.999999' would be to use the TIMESTAMP and REPATTERN built-in functions as follows:

repattern( timestamp(),
                'YYYY-MM-DD HH:MI:SS.999999',
                'YYYY-MM-DD-HH.MI.SS.999999' )

Table 2. Date/time patterns
	Four-digit years	Two-digit years
Year first	YYYYMMDD YYYYMMMDD YYYYMmmDD YYYYDDD YYYYMM YYYYMMM YYYYMmm YYYY YYYYMMDDHHMISS999 YYYY-MM-DD-HH.MI.SS.999999 YYYY-MM-DD HH:MI:SS.999999	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
without zeros		ZY-ZM-ZD YY-ZM-ZD ZM/ZD/ZY ZM/ZD/YY ZD.ZM.ZY ZD.ZM.YY

Table 3. Time-only patterns
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.