Date/Time Built-in Functions

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
Note: For the three-letter month patterns, the uppercase/lowercase characters must correspond exactly.

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
  • YYYYMMDD
  • YYYYMMMDD
  • YYYYMmmDD
  • YYYYDDD
  • YYYYMM
  • YYYYMMM
  • YYYYMmm
  • YYYY
  • YYYY/MM/DD
  • YYYYMMDDHHMISS999
  • YYYY-MM-DD-HH.MI.SS.999999
  • YYYY-MM-DD HH:MI:SS.999999
  • YYMMDD
  • YYMMMDD
  • YYMmmDD
  • YYDDD
  • YYMM
  • YYMMM
  • YYMmm
  • YY
  • YY/MM/DD
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

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

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.