TIMESTAMP_FORMAT

The TIMESTAMP_FORMAT function returns a timestamp that is based on the interpretation of the input string using the specified format.

Read syntax diagramSkip visual syntax diagramTIMESTAMP_FORMAT(string-expression ,format-string ,6,precision-constant )
string-expression
An expression that returns a value of any built-in character string data type or graphic string data type.

The string is interpreted as a date or timestamp using the format specified by format-string. Start of changeThe string-expression must contain only the components of a date or timestamp that correspond to the format elements specified by format-string.End of change

format-string
An expression that returns a built-in character string data type or graphic string data type. format-string contains a template of how string-expression is to be interpreted as a date or a timestamp value.

A valid format-string must contain at least one format element, must not contain multiple specifications for any component of a date or a timestamp, and can contain any combination of the format elements, unless otherwise noted in Table 1. For example, format-string cannot contain both YY and YYYY, because they are both used to interpret the year component of string-expression. Refer to the table to determine which format elements cannot be specified together.

Two format elements can optionally be separated by one or more of the following separator characters:
  • minus sign (-)
  • period (.)
  • slash (/)
  • comma (,)
  • apostrophe (′)
  • semicolon (;)
  • colon (:)
  • blank ( )

Separator characters can also be specified at the start or end of format-string. These separator characters can be used in any combination in the format string, for example 'YYYY/MM-DD HH24:MM.SS'. Separator characters specified in a string-expression are used to separate components and are not required to match the separator characters specified in the format-string.

Table 1. Format elements for the TIMESTAMP_FORMAT function
Format element Related components of a timestamp Description
Start of changeAM or PM 1, 2End of change Start of changehourEnd of change Start of changeMeridian indicator (morning or evening) without periods. The meridian indicator is retrieved from message CPX9035 in message file QCPFMSG in library *LIBL.End of change
Start of changeA.M. or P.M. 1, 2End of change Start of changehourEnd of change Start of changeMeridian indicator (morning or evening) with periods. This format element uses the exact strings ‘A.M.' or ‘P.M.' and is independent of the language used for messages in the job.End of change
Start of changeDAY, Day, or day 1, 3End of change Start of changenoneEnd of change Start of changeName of the day in uppercase, titlecase, or lowercase format. The name of the day is retrieved from message CPX9034 in message file QCPFMSG in library *LIBL.End of change
Start of changeDY, Dy, or dy 1, 3End of change Start of changenoneEnd of change Start of changeAbbreviated name of the day in uppercase, titlecase, or lowercase format. The abbreviated name of the day is retrieved from message CPX9039 in message file QCPFMSG in library *LIBL.End of change
Start of changeD 1, 3End of change Start of changenoneEnd of change Start of changeDay of week (1-7), where 1 is Sunday.End of change
DD day Day of month (01-31).
Start of changeDDDEnd of change Start of changemonth, dayEnd of change Start of changeDay of year (001-366).End of change
FF or FFn fractional seconds Fractional seconds (0-999999999999). The number n is used to specify the number of digits expected in the string-expression. Valid values for n are 1-12. Specifying FF is equivalent to specifying FF6. When the component in string-expression that corresponds to the FF format element is followed by a separator character or is the last component, the number of digits for the fractional seconds can be less than what is specified by the format element. In this case, zero digits are padded onto the right of the specified digits.
Start of changeHHEnd of change Start of changehourEnd of change Start of changeHH behaves the same as HH12.End of change
Start of changeHH12End of change Start of changehourEnd of change Start of changeHour of the day (01-12) in 12-hour format. AM is the default meridian indicator.End of change
HH24 hour Hour of the day (00-24) in 24-hour format.
Start of changeJEnd of change Start of changeyear, month, and dayEnd of change Start of changeJulian date (number of days since January 1, 4713 BC).End of change
MI minute Minute (00-59).
MM month Month (01-12).
Start of changeMONTH, Month, or month 1End of change Start of changemonthEnd of change Start of changeName of the month in uppercase, titlecase, or lowercase format. The name of the month is retrieved from message CPX3BC0 in message file QCPFMSG in library *LIBL.End of change
Start of changeMON, Mon, or mon 1End of change Start of changemonthEnd of change Start of changeAbbreviated name of the month in uppercase, titlecase, or lowercase format. The name of the month is retrieved from message CPX8601 in message file QCPFMSG in library *LIBL.End of change
NNNNNN microseconds Microsecond (same as FF6).
RR 4 year Last 2 digits of the adjusted year (00-99).
Start of changeRRRR 4End of change Start of changeyearEnd of change Start of changeFour digit adjusted year (0000-9999).End of change
SS seconds Seconds (00-59).
Start of changeSSSSSEnd of change Start of changehours, minutes, and secondsEnd of change Start of changeSeconds since previous midnight (00000-86400).End of change
Start of changeYEnd of change Start of changeyearEnd of change Start of changeLast digit of the year (0-9). First three digits of the current year are used to determine the full 4-digit year.End of change
YY year Last 2 digits of the year (00-99). First two digits of the current year are used to determine the full 4-digit year.
Start of changeYYYEnd of change Start of changeyearEnd of change Start of changeLast three digits of the year (000-999). First digit of the current year is used to determine the full 4-digit year.End of change
YYYY year 4-digit year (0000-9999).
Notes:
  1. Only these exact spellings and case combinations can be used. If this format element is specified in an invalid case combination an error is returned.
  2. The AM and PM set of meridian indicators can be used interchangeably in the format-string, as can A.M. and P.M. If HH24 is used in the format-string along with a meridian indicator, the value of the meridian indicator in the string-expression is not used for determining the hour portion of the resulting timestamp.
  3. The DAY, Day, day DY, Dy, dy, and D format elements do not contribute to any components of the resulting timestamp. However, a specified value for any of these format elements must be correct for the combination of the year, month, and day components of the resulting timestamp. For example, a value of 'Monday 2008-10-06' for string-expression is valid for a value of 'Day YYYY-MM-DD'. However, a value of 'Tuesday 2008-10-06' for string-expression would result in an error for the same format-string.
  4. The RR and RRRR format elements can be used to alter how a specification for a year is to be interpreted by adjusting the value to produce a 2-digit or a 4-digit value depending on the leftmost two digits of the current year according to the following table:
    Last two digits of current year Two digits of year in string-expression First 2 digits of the year component of date or timestamp
    0-50 0-49 First 2 digits of current year
    51-99 0-49 First 2 digits of current year + 1
    0-50 50-99 First 2 digits of current year - 1
    51-99 50-99 First 2 digits of current year

    For example, if the current year is 2007, '86' with format 'RR' means 1986, but if the current year is 2052, it means 2086.

The following defaults will be used when a format-string does not include a format element for one of the components of a timestamp:

Timestamp component Default
year current year, as 4 digits
month current month, as 2 digits
day 01 (first day of the month)
hour 00
minute 00
second 00
fractional seconds a number of zeros matching the timestamp precision of the result

Start of changeIf string-expression does not include a value that corresponds to an hour, minute, second, or fractional seconds format element that is specified in the format-string, these same defaults are used.End of change

Leading zeros can be specified for any component of the date or timestamp value (for example, month, day, hour, minutes, seconds) that does not have the maximum number of significant digits for the corresponding format element in the format-string.

A substring of the string-expression representing a component of a date or timestamp (such as year, month, day, hour, minutes, seconds) can include less than the maximum number of digits for that component of the date or timestamp. Any missing digits default to zero. For example, with a format-string of 'YYYY-MM-DD HH24:MI:SS', an input value of '999-3-9 5:7:2' would produce the same result as '0999-03-09 05:07:02'.

precision-constant
An integer constant that specifies the timestamp precision of the result. The value must be in the range 0 to 12. If precision-constant is not specified, the timestamp precision defaults to 6.

The result is a timestamp with a precision that is based on precision-constant. If either of the first two arguments can be null, the result can be null; if either of the first two arguments is null, the result is the null value.

Notes

Julian and Gregorian calendar: The transition from the Julian calendar to the Gregorian calendar on 15 October 1582 is taken into account by this function.

Syntax alternatives: TO_DATE is a synonym for TIMESTAMP_FORMAT. Start of changeTO_TIMESTAMP is identical to TIMESTAMP_FORMAT except that if precision-constant is not specified, the timestamp precision of the result defaults to 12.End of change

Examples

  • Start of changeInsert a row into the IN_TRAY table with a receiving timestamp that is equal to one second before the beginning of the year 2000 (December 31, 1999 at 23:59:59).
    INSERT INTO IN_TRAY (RECEIVED)
         VALUES (TIMESTAMP_FORMAT('1999-12-31 23:59:59',
           'YYYY-MM-DD HH24:MI:SS'))
    End of change
  • An application receives strings of date information into a variable called INDATEVAR. This value is not strictly formatted and might include two or four digits for years, and one or two digits for months and days. Date components might be separated with minus sign (-) or slash (/) characters and are expected to be in day, month, and year order. Time information consists of hours (in 24-hour format) and minutes, and is usually separated by a colon. Sample values include '15/12/98 13:48' and '9-3-2004 8:02'. Insert such values into the IN_TRAY table.
    INSERT INTO IN_TRAY (RECEIVED)
         VALUES (TIMESTAMP_FORMAT(:INDATEVAR,
           'DD/MM/RRRR HH24:MI'))
    The use of RRRR in the format allows for 2- and 4-digit year values and assigns missing first two digits based on the current year. If YYYY were used, input values with a 2-digit year would have leading zeros. The slash separator also allows the minus sign character. Assuming a current year of 2007, resulting timestamps from the sample values are:
    '15/12/98 13:48' --> 1998-12-15-13.48.00.000000
    '9-3-2004 8:02'  --> 2004-03-09-08.02.00.000000
  • Set the character variable TVAR to the value of ROUTINE_CREATED from QSYS2.SYSPROCS if it is equal to one second before the beginning of the year 2000 ('1999-12-31 23:59:59'). The character string should be interpreted according to the format string provided.
    SELECT VARCHAR_FORMAT(ROUTINE_CREATED, 'YYYY-MM-DD HH24:MI:SS')
         INTO :TVAR
         FROM QSYS2.SYSPROCS
         WHERE ROUTINE_CREATED = 
                    TIMESTAMP_FORMAT('1999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
  • Return timestamp values for strings containing meridian indicators:
    string-expression format-expression Result timestamp value
    '2015-10-28 10:29AM' 'YYYY-MM-DD HH12:MIAM' 2015-10-28-10.29.00.000000
    '2015-10-28 10:29PM' 'YYYY-MM-DD HH12:MIAM' 2015-10-28-22.29.00.000000
    '2015-10-28 10:29AM' 'YYYY-MM-DD HH24:MIAM' 2015-10-28-10.29.00.000000
    '2015-10-28 10:29PM' 'YYYY-MM-DD HH24:MIAM' 2015-10-28-10.29.00.000000
    '2015-10-28 22:29AM' 'YYYY-MM-DD HH24:MIAM' 2015-10-28-22.29.00.000000
    '2015-10-28 22:29PM' 'YYYY-MM-DD HH24:MIAM' 2015-10-28-22.29.00.000000