The following operations require the specification of a datetime format:

• Converting date data, time data, or time stamp data into a non-predefined character string representation using the VARCHAR_FORMAT scalar function
• Converting a non-predefined character string representation of a date, time, or a time stamp into date data, time data, or time stamp data using the DATE, TIME, or TIMESTAMP_FORMAT scalar function

1. The following items can be specified as a datetime format:
   
   • Character string literals and mixed character string literals
   • Column specifications
   • Component specifications
   • SQL variables or SQL parameters
   • Concatenation operation
   • Set functions (MAX and MIN)
   • Scalar functions
   • CASE expressions
   • CAST specifications
   • Function calls
   • Scalar subquery
2. The data type of the datetime format should be either the character data type (CHAR, VARCHAR) or the mixed character data type (MCHAR, MVARCHAR).
3. The maximum allowable length of the datetime format is 240 bytes.

1. Table 1-28 shows elements of the datetime format that can be specified in a datetime format, and their meanings:
   

   Table 1-28 Datetime format elements and their meanings

   Datetime item	Format item1	Meaning
   Year	YYYY	A 4-digit year (0001-9999)
   	YY	A 2-digit year (00-99)4
   Month	MM	Month (01-12)
   	MON	Month, abbreviated2, 3
   	MONTH	Name of the month2, 3
   Day	DD	Day (01 to the last day of the month)
   Hour	HH	Hour (00-23)
   Minute	MI	Minute (00-59)
   Second	SS	Second (00-59)
   Fractional second	FF	Fractional second4, 5
   	NN...N	Fractional second in p digits (p = N, where N is 1-6)6
   Other	Space ( )	Elements that can be used as delimiter characters
   	Hyphen (-)
   	Forward slash (/)
   	Comma (,)
   	Period (.)
   	Semicolon (;)
   	Colon (:)
   	"character-string"	A character string enclosed in double quotation marks that denotes the character string itself7

¹ All elements of the datetime format, with the exception of a character string enclosed in double quotation marks (") should be specified in single-byte characters. All characters with the exception of the first and second characters of MON and MONTH, and characters other than those in a character string enclosed in double quotation marks, are not case-sensitive.

² In MON and MONTH, you can specify an abbreviated name of the month and also whether the name of a month is spelled in upper or lower case characters. The determination of upper case vs. lower case is based on the first and second characters of a specified datetime element.

Examples

MONTHJUNE
MonthJune
monthjune
 

Table 1-29 shows the relationship between the first and second characters of the datetime format element MON or MONTH, the name of a month, and the format of an abbreviated name.

Table 1-29 Relationship between the first and second characters of the datetime format element MON or MONTH, the name of a month, and the format of an abbreviated month

Second character	First character
	Upper case	Lower case
Upper case	All upper case	All lower case
Lower case	Upper case in the first character only	All lower case

³ Table 1-30 shows the abbreviated name and the full name of each month when a datetime format element is specified using MON or MONTH.

Table 1-30 Abbreviated and full names of each month (when a datetime format element is specified in MON or MONTH)

Month	Abbreviated name	Name
1	JAN	JANUARY
2	FEB	FEBRUARY
3	MAR	MARCH
4	APR	APRIL
5	MAY	MAY
6	JUN	JUNE
7	JUL	JULY
8	AUG	AUGUST
9	SEP	SEPTEMBER
10	OCT	OCTOBER
11	NOV	NOVEMBER
12	DEC	DECEMBER

⁴ The items YY and FF can be used only in the VARCHAR_FORMAT scalar function; when specified in other scalar functions, they can cause an error.

⁵ With the item FF, the number of digits in the fractional second part of the resulting character string representation is governed by the type of the time stamp data that is specified in an argument in the VARCHAR_FORMAT scalar function. If the precision of the fractional second is 0, a character string with a zero length is produced.

⁶ The item NN...N is converted in the following format:

• Converting a datetime value into a character string representation:
  If p is smaller than the fractional second precision of time stamp data, the data is truncated; if p is larger, the expanded fractional second part is zero-filled.
  
• Converting a character string representation into a datetime value:
  The number of digits in the fractional second part of the character string representation must agree with p.
  

⁷ Any double quotation mark specified in a character string enclosed in double quotation marks must be expressed as two successive double quotation marks ("").

1. In a character string in a datetime format, elements of the datetime format of a datetime item, with the exception of delimiter characters and character strings enclosed in double quotation marks, can be specified only once.
2. During conversion of datetime data into a character string expression, if a datetime format element unrelated to the datetime data to be converted is present, that part is complemented by the character strings listed in Table 1-31.

Example

VARCHAR_FORMAT(DATE('2002-01-01'),'YYYY-MM-DD HH:MI')
-> '2002-01-01 00:00'
 

Table 1-31 lists character strings that are used to complement datetime format elements.

Table 1-31 Character strings complementing datetime format elements

Datetime format element	Complementing character string
YYYY	Current year (e.g., '2002')
YY	Last two digits of the current year (e.g., '02')
MM	Current month (e.g., '08')
MON	Current month, abbreviated name (e.g., 'AUG')
MONTH	Name of the current month (e.g., 'AUGUST')
DD	Current day (e.g., '05')
HH	'00'
MI	'00'
SS	'00'
FF	'00'
NN...N	'00...0' (a string of p zeros, where the number of zeros is equal to the value of p = N)

1. Table 1-32 shows the datetime items that are required by the scalar function that converts a given character string representation into a datetime value. An error occurs if a required datetime item is missing.
   

   Table 1-32 Datetime items that are required by the scalar function that converts a given character string representation into a datetime value

   Scalar function	Required datetime items
   DATE	Year, month, day
   TIME	Hour, minute, second
   TIMESTAMP_FORMAT	Year, month, day, hour, minute, second

2. During the process of converting a character string representation into a datetime value, datetime format elements that are not relevant to converted data do not appear in the results.
3. During the process of converting a character string representation into a datetime value, any space that is not in the specified datetime format but that occurs between format elements in the character string is ignored.
4. Table 1-33 shows the relationship between scalar functions in which a datetime format can be specified and elements of the datetime format.
   

   Table 1-33 Relationship between scalar functions in which a datetime format can be specified and elements of the datetime format

   Elements of datetime format	VARCHAR_FORMAT			DATE	TIME	TIMESTAMP_FORMAT
   	DATE type1	TIME type1	TIMESTAMP type1
   YYYY	Y2	Y2 (Current year)	Y2	R	Y5	R
   YY	Y2	Y2 (Last 2 digits of current year)	Y2	N	N	N
   MM	Y3	Y3 (Current month)	Y3	R3	Y3, 5	R3
   MON	Y3	Y3 (Abbr. name of current month)	Y3	R3	Y3, 5	R3
   MONTH	Y3	Y3 (Name of current month)	Y3	R3	Y3, 5	R3
   DD	Y	Y (Current day)	Y	R	Y5	R
   HH	Y ('00')	Y	Y	Y5	R	R
   MI	Y ('00')	Y	Y	Y5	R	R
   SS	Y ('00')	Y	Y	Y5	R	R
   FF	Y4 ('00')	Y4 ('00')	Y4	N	N	N
   NN...N	Y4 ('00...0')	Y4 ('00...0')	Y4	Y5	Y5	Y

Legend:

R: Required and can be specified only once. It causes an error if not specified.

Y: Can be specified only once.

N: Cannot be specified; it causes an error if specified.

( ): Parentheses indicate the character string to be converted.

¹ Indicates the conversion of data of the type specified in the VARCHAR_FORMAT scalar function into a character string representation.

² Either YYYY or YY, but not both, can be specified only once.

³ Only one of MM, MON, and MONTH can be specified only once.

⁴ Either FF or NN...N, but not both, can be specified only once.

⁵ This item, not relevant to the data type of the result, does not appear in the result.