TIMESTAMP_FORMAT

string-expression

An expression that returns a value of any built-in character string data type or graphic string data type.

The resulting substring is interpreted as a a date or timestamp using the format specified by format-string.

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.

The elements of format-sting are not case sensitive. 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 HH: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
AM or PM 1, 2	hour	Meridian indicator (morning or evening) without periods. The meridian indicator is retrieved from message CPX9035 in message file QCPFMSG in library *LIBL.
A.M. or P.M. 1, 2	hour	Meridian 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.
DAY, Day, or day 1, 3	none	Name 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.
DY, Dy, or dy 1, 3	none	Abbreviated 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.
D 1, 3	none	Day of week (1-7), where 1 is Sunday.
DD	day	Day of month (01-31).
DDD	month, day	Day of year (001-366).
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.
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 date (number of days since January 1, 4713 BC).
MI	minute	Minute (00-59).
MM	month	Month (01-12).
MONTH, Month, or month 1	month	Name 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.
MON, Mon, or mon 1	month	Abbreviated 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.
NNNNNN	microseconds	Microsecond (same as FF6).
RR 4	year	Last 2 digits of the adjusted year (00-99).
RRRR 4	year	Four digit adjusted year (0000-9999).
SS	seconds	Seconds (00-59).
SSSSS	hours, minutes, and seconds	Seconds since 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 2 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).

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

If string-expression does not include a value that corresponds to an hour, minute, second, or microseconds format element that is specified in the format-string, these same defaults are used.

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'.

The result of the function is a timestamp. If the either argument can be null, the result can be null; if the either argument is null, the result is the null value.

Note

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 and TO_TIMESTAMP are synonyms for TIMESTAMP_FORMAT.