DB2 10.5 for Linux, UNIX, and Windows

TIMESTAMP_FORMAT scalar function

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.

string-expression

The expression must return a value that is a built-in CHAR or VARCHAR data type. In a Unicode database, if a supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function. The string-expression must contain the components of a timestamp that correspond to the format specified by format-string.

format-string

The expression must return a value that is a built-in CHAR or VARCHAR data type. In a Unicode database, if a supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function. The actual length must not be greater than 254 bytes (SQLSTATE 22007). 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 Table 1 (SQLSTATE 22007). 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 (')
semi-colon (;)
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	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).

Note: The format elements in Table 1 are not case sensitive, except for the following:

AM, PM
A.M., P.M.
DAY, Day, day
DY, Dy, dy
D
MONTH, Month, month
MON, Mon, mon

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

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 matching the timestamp precision of the result

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

precision-constant

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

locale-name

A character constant that specifies the locale used for the following format elements:

AM, PM
DAY, Day, day
DY, Dy, dy
D
MONTH, Month, month
MON, Mon, mon

The value of locale-name is not case sensitive and must be a valid locale (SQLSTATE 42815). For information about valid locales and their naming, see "Locale names for SQL and XQuery". If locale-name is not specified, the value of the special register CURRENT LOCALE LC_TIME is used.

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.

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.
Determinism: TIMESTAMP_FORMAT is a deterministic function. However, the following invocations of the function depend on the value of either the special register CURRENT LOCALE LC_TIME or CURRENT TIMESTAMP.
- When format-string is not explicitly specified, or when locale-name is not explicitly specified and one of the following is true:
  - format-string is not a constant
  - format-string is a constant and includes format elements that are locale sensitive
  - format-string is a constant and does not include a format element that fully defines the year (that is, J or YYYY) and so uses the value of the current year
  - format-string is a constant and does not include a format element that fully defines the month (for example, J, MM, MONTH, or MON) and so uses the value of the current month
These invocations that depend on the value of a special register cannot be used wherever special registers cannot be used (SQLSTATE 42621, 428EC, or 429BX).
Syntax alternatives: TO_DATE and TO_TIMESTAMP are synonyms for TIMESTAMP_FORMAT.

Examples

Example 1: Insert 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'))
```
Example 2: 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
```