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. However, if the value expression being converted is in the UTF-16 character set, the maximum is 480 bytes.

1. The following table lists the datetime format elements and their meanings.
   

   Table 1-30 Datetime format elements and their meanings

   Datetime item	Format item#1	Meaning
   Year	YYYY	A 4-digit year (0001 to 9999)
   	YY	A 2-digit year (00 to 99)#4
   Month	MM	Month (01 to 12)
   	MON	Month, abbreviated#2, #3
   	MONTH	Name of the month#2, #3
   Day	DD	Day (01 to the last day of the month)
   Hour	HH	Hour (00 to 23)
   Minute	MI	Minute (00 to 59)
   Second	SS	Second (00 to 59)#8
   Fractional second	FF	Fractional second#4, #5
   	NN...N	Fractional second in p digits (p = N, where N is 1 to 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 itself#7

#1: All elements of the datetime format, with the exception of a character string enclosed in double quotation marks (") must 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.

#2: 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 versus lower case is based on the first and second characters of a specified datetime element.

Examples

MONTHJUNE
MonthJune
monthjune
 

The following table indicates 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 month.

Table 1-31 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

#3

The following table indicates the abbreviated and full names of each month (when a datetime format element is specified in MON or MONTH).

Table 1-32 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

#4: 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.

#5: 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 of length zero is produced.

#6: 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.
  

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

#8: If you set the system common definition operand pd_leap_second to allow leap seconds to be specified, the range for seconds is as shown below. For details about the system common definition operand pd_leap_second, see the manual HiRDB Version 9 System Definition.

Datetime item	Datetime format element	Meaning
Second	SS	Second (00 to 61)

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. When converting from datetime to a character string representation, if you specify datetime format elements for which there are no related datetime data to be converted, those unrelated parts are filled with the compatible character strings listed in the table below.

Example

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

1. The following table indicates the character strings compatible with datetime format elements.
   

   Table 1-33 Character strings compatible with datetime format elements

   Datetime format element	Compatible 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)

2. The table below lists 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-34 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

3. 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.
4. 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.
5. The following table indicates the relationship between scalar functions in which a datetime format can be specified and datetime format elements.
   

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

   Datetime format element	VARCHAR_FORMAT			DATE	TIME	TIMESTAMP_FORMAT
   	DATE type#1	TIME type#1	TIMESTAMP type#1
   YYYY	Y#2	Y#2 (Current year)	Y#2	R	Y#5	R
   YY	Y#2	Y#2 (Last 2 digits of current year)	Y#2	N	N	N
   MM	Y#3	Y#3 (Current month)	Y#3	R#3	Y#3, #5	R#3
   MON	Y#3	Y#3 (Abbr. name of current month)	Y#3	R#3	Y#3, #5	R#3
   MONTH	Y#3	Y#3 (Name of current month)	Y#3	R#3	Y#3, #5	R#3
   DD	Y	Y (Current day)	Y	R	Y#5	R
   HH	Y ('00')	Y	Y	Y#5	R	R
   MI	Y ('00')	Y	Y	Y#5	R	R
   SS	Y ('00')	Y	Y	Y#5	R	R
   FF	Y#4 ('00')	Y#4 ('00')	Y#4	N	N	N
   NN...N	Y#4 ('00...0')	Y#4 ('00...0')	Y#4	Y#5	Y#5	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.

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

#2: Either YYYY or YY, but not both, can be specified only once.

#3: Only one of MM, MON, and MONTH can be specified only once.

#4: Either FF or NN...N, but not both, can be specified only once.

#5: This item, not relevant to the data type of the result, does not appear in the result.