TIMESTAMP_FORMAT

The TIMESTAMP_FORMAT function returns a timestamp.

Read syntax diagramSkip visual syntax diagram
>>-TIMESTAMP_FORMAT--(--string-expression--,--format-string--)-><

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 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 timestamp value. The resulting value is then folded to uppercase, so the characters in the value may be in any case. The resulting substring must:
  • Contain at least one format element and must not contain multiple specifications for any component of a timestamp.
  • Each portion of the format must be separated by a valid separator. Valid separators are
    • dash (-)
    • period (.)
    • slash (/)
    • comma (,)
    • apostrophe (′)
    • semicolon (;)
    • colon (:)
    • blank ( )
Format Unit
DD Day
HH24 Hour
MI Minute
MM Month
NNNNNN Microsecond
RR Last 2 digits of the adjusted year (00-99).
SS Seconds
YY Last 2 digits of the year (00-99).
YYYY Year
The RR format element can be used to alter how a specification for a year is to be interpreted by adjusting the value to produce a 4-digit value depending on the last 2 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 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
The following defaults will be used when a format string does not include a format element for one of the components of a timestamp:
year          current year
month         current month
day           01
hour          00
minute        00
second        00
microsecond   00
Leading zeroes can be specified for any component of the 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 timestamp (such as year, month, day, hour, minutes, seconds) can include less than the maximum number of digits for that component of the 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'.

Examples of valid format strings are:

'YYYY-MM-DD'
'YYYY-MM-DD-HH24-MI-SS'
'YYYY-MM-DD-HH24-MI-SS-NNNNNN'

The result is timestamp. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.

Note

Determinism: TIMESTAMP_FORMAT is a non-deterministic function.

Syntax alternatives: TO_DATE is a synonym for TIMESTAMP_FORMAT.

Example