The TIMESTAMP_FORMAT function returns a timestamp that is based on the interpretation of the input string using the specified format.
>>-TIMESTAMP_FORMAT--(--string-expression-----------------------> >--+---------------------------------------------------------------------+--)->< '-,--format-string--+-----------------------------------------------+-' | .-,--6------------------. | +-+-----------------------+--+----------------+-+ | '-,--precision-constant-' '-,--locale-name-' | | .-,--6------------------. | '-,--locale-name--+-----------------------+-----' '-,--precision-constant-'
The schema is SYSIBM.
Format element | Related components of a timestamp | Description |
---|---|---|
AM or PM | hour | Meridian indicator (morning or evening) without periods. This format element is dependent on locale-name, if specified. Otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. |
A.M. or P.M. | 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 locale name in effect. |
DAY, Day, or day | none | Name of the day in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. |
DY, Dy, or dy | none | Abbreviated name of the day in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. |
D | none | Day of the week (1-7). The first day of the week is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. |
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 with no leading zeros. Specifying FF is equivalent to specifying FF6. When the component in string-expression corresponding 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 day (number of days since January 1, 4713 BC). |
MI | minute | Minute (00-59). |
MM | month | Month (01-12). |
MONTH, Month, or month | month | Name of the month in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. |
MON, Mon, or mon | month | Abbreviated name of the month in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. |
NNNNNN | microseconds | Microseconds (000000-999999). Same as FF6. |
RR | year | Last two digits of the adjusted year (00-99). |
RRRR | year | 4-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 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). |
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 (SQLSTATE 22007). For example, assuming a value of 'en_US' is used for locale-name, a value of 'Monday 2008-10-06' for string-expression is valid for a value of 'Day YYYY-MM-DD'. However, value of 'Tuesday 2008-10-06' for string-expression would result in error for the same format-string.
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 value or a 4-digit value depending on the leftmost two digits of the current year according to the following table.
Last two 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 |
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 |
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 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' would produce the same result as '0999-03-09 05:07:02'.
If format-string is not specified, string-expression will be interpreted using a default format based on the value of the special register CURRENT LOCALE LC_TIME.
The result of the function is a TIMESTAMP with a precision 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.
INSERT INTO IN_TRAY (RECEIVED)
VALUES (TIMESTAMP_FORMAT('1999-12-31 23:59:59',
'YYYY-MM-DD HH24:MI:SS'))
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