VARCHAR_FORMAT

The VARCHAR_FORMAT function returns a character representation of a timestamp in the format indicated by format-string.

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

expression
An expression that returns a value of one of the following built-in data types: a timestamp, a character string, or a graphic string.

If expression is a character or graphic string, the value of expression must be a valid string representation of a timestamp. For the valid formats of string representations of timestamps, see String representations of datetime values.

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 expression is to be formatted. The resulting value is then folded to uppercase, so the characters in the value may be in any case. A valid format is any combination of the formats listed below optionally separated by valid separators Valid separators are
  • dash (-)
  • period (.)
  • slash (/)
  • comma (,)
  • apostrophe (′)
  • semicolon (;)
  • colon (:)
  • blank ( )
Format Unit
CC Century (00-99). If the last two digits of the four digit year are zero, the result is the first two digits of the year. Otherwise, the result is the first two digits of the year plus one.
D Day of week (1-7).
DD Day of month (01-31).
DDD Day of year (001-366).
FF[n] Start of changeFractional seconds (000000-999999). The number n is used to specify the number of digits to include in the value returned. Valid values for n are 1-6. The default is 6.End of change
HH24 Hour of the day (00-24).
ID ISO day of week (1-7), where 1 is Monday and 7 is Sunday
IW ISO week of year (01-53). The week starts on Monday and includes 7 days. Week 1 is the first week of the year to contain a Thursday, which is equivalent to the first week of the year to contain January 4
IYYY ISO year (0000-9999). The year based on the ISO week that is returned.
J Julian date (0000000-9999999).
MI Minute (00-59).
MM Month (01-12).
NNNNNN Microseconds (000000-999999).
Q Quarter (1-4).
SS Seconds (00-59).
SSSSS Seconds since previous midnight (00000-86400).
W Week of month (1-5).
Start of changeWWEnd of change Start of changeWeek of the year (01-53), where week 1 starts on January 1 and ends on January 7.End of change
YYYY Year (0000-9999).

Examples of valid format strings are:

'HH24-MI-SS'
'HH24-MI-SS-NNNNNN'
'YYYY-MM-DD'
'YYYY-MM-DD-HH24-MI-SS'
'YYYY-MM-DD-HH24-MI-SS-NNNNNN'
'FF3.J/Q-YYYY'

Start of changeThe result is a representation of expression in the format specified by format-string. format-string is interpreted as a series of format elements that can be separated by one or more separator characters. A string of characters in format-string is interpreted as the longest format element that matches an element in the previous table. If two format elements are composed of the same character and they are not separated by a separator character, DB2® interprets the specification starting from the left, as the longest element that matches an element from the previous table, and continues until matches are found for the remainder of the format string. For example, 'DDDDD' is interpreted as the format elements, 'DDD' and 'DD'.End of change

The data type of the result is same as the data type of the format-string. The length attribute of the result is the maximum of Start of change255End of change and the length attribute of the format-string. format-string also determines the actual length of the result. Start of changeThe actual length must not be greater than the length attribute of the result.End of change If either argument can be null, the result can be null; if either argument is null, the result is the null value.

The CCSID of the result is same as the CCSID of the format-string.

Note

Syntax alternatives: TO_CHAR is a synonym for VARCHAR_FORMAT.

Example