The VARCHAR_FORMAT function returns a character representation of a timestamp in the format indicated by format-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 | 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] | Fractional 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.![]() |
| 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). |
WW![]() |
Week of the year (01-53), where week 1 starts
on January 1 and ends on January 7.![]() |
| 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'
The 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'.
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
255
and the length attribute of the format-string. format-string also
determines the actual length of the result.
The
actual length must not be greater than the length attribute of the
result.
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.
Syntax alternatives: TO_CHAR is a synonym for VARCHAR_FORMAT.
SELECT VARCHAR_FORMAT(RECEIVED,'YYYY-MM-DD HH24:MI:SS') INTO :TVAR FROM CORPDATA.IN_TRAY