TIMESTAMP_FORMAT

The schema is SYSIBM.

string-expression

An expression that returns a value of any built-in character or graphic string data type, other than a CLOB or DBCLOB, with an actual length that is not greater than 255 bytes. The string-expression must contain the components of a timestamp that correspond to the format that is specified in format-string, except for hour, minute, second, or fractional seconds.

format-string

The expression must return a value that is a built-in character or graphic string data type, other than a CLOB or DBCLOB, with a length attribute that is not greater than 255 bytes. The actual length must not be greater than 255 bytes. The value is a template for how string-expression is interpreted and then converted to a timestamp value.

A valid format-string must contain at least one format element, must not contain multiple specifications for any component of a timestamp, and can contain any combination of the format elements, unless otherwise noted in the following table. For example, format-string cannot contain both YY and YYYY, because both are used to interpret the year component of a string-expression. Two format elements can be separated by one or more of the following separator characters:

• minus sign (-)
• period (.)
• forward slash (/)
• comma (,)
• apostrophe (')
• semicolon (;)
• colon (:)
• blank ( )

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

Table 1. Format elements for the TIMESTAMP_FORMAT function

Format element	Related component of a timestamp	Description
AM or PM 1	hour	Meridian indicator (morning or evening) without periods. This format element uses the exact strings "AM" or "PM".
A.M. or P.M. 1	hour	Meridian indicator (morning or evening) with periods. This format element uses the exact strings "A.M." or "P.M."
D 1	none	Day of the week (1-7).
DD	day	Day of the month (0-31).
DDD	month, day	Day of the year (001-366).
FF or FFn	fractional seconds	Fractional seconds (0-999999999999). The number n is used to specify the number of digits that is expected in the string-expression. Valid values for n are 1-12 with no leading zeros. 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 number of specified digits.
HH	hour	HH behaves the same as HH12.
HH12	hour	Hour of the day (01-12) in 12-hour format. AM is the default meridian indicator.
HH24	hour	Hour of the day (00-24) in 24-hour format.
J	year, month, and day	Julian day (number of days since January 1, 4713 BC).
MI	minute	Minute (00-59).
MM	month	Month (01-12).
MONTH, Month, or month 1, 2	month	Name of the month in English.
MON, Mon, or mon 1, 2	month	Abbreviated name of the month in English.
NNNNNN	microseconds	Microseconds (000000-999999).
RR	year	Last two digits of the adjusted year (00-99).
RRRR	year	Four digit adjusted year (0000-9999).
SS	seconds	Seconds (00-59).
SSSSS	hours, minutes, and seconds	Seconds since the previous midnight (00000 - 86400).
Y	year	Last digit of the year (0-9). First three digits of the current year are used to determine the full 4-digit year.
YY	year	Last two digits of the year (00-99). First two digits of the current year are used to determine the full 4-digit year.
YYY	year	Last three digits of the year (000-999). First digit of the current year is used to determine the full 4-digit year.
YYYY	year	4-digit year (0000-9999).
Notes: This format element is case sensitive. 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. The D format element does not contribute to any components of the resulting timestamp. However, a specified value for this format element must be correct for the combination of the day component of the resulting timestamp. For example, a value of '5' for string-expression is valid for a format string value of 'D'. However, value of '9' for string-expression would result in an error for the same format-string.

The RR and RRRR format elements can be used to change 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:

Table 2. Correspondence of adjusted year value and timestamp component

Digits of the current year	Two-digit year in string-expression	First two digits of the year component of timestamp
00-50	00-49	First two digits of the current year
51-99	00-49	First two digits of the current year + 1
00-50	50-99	First two digits of the current year -1
51-99	50-99	First two digits of the 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 are used when a format-string does not include a format element for one of the following 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 to match the timestamp precision of the result

If 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, the same defaults are used.

Leading zeros can be specified for any component of the timestamp value (that is, 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 that represents a component of a timestamp (such as year, month, day, hour, minutes, seconds) can include fewer than the maximum number of digits for that component of the timestamp that is indicated by the corresponding format element. 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' produces 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 of the function 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.

The result CCSID is the appropriate CCSID of the encoding scheme of the first argument and the result subtype is the appropriate subtype of the CCSID.