1.11 Specifying a datetime format

Organization of this section
(1) Overview
(2) Rules for the datetime format
(3) Elements of the datetime format
(4) Rules for datetime format elements

(1) Overview

The following operations require the specification of a datetime format:

(2) Rules for the datetime format

  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.

(3) Elements of the datetime format

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

    Table 1-30 Datetime format elements and their meanings

    Datetime itemFormat item#1Meaning
    YearYYYYA 4-digit year (0001 to 9999)
    YYA 2-digit year (00 to 99)#4
    MonthMMMonth (01 to 12)
    MONMonth, abbreviated#2, #3
    MONTHName of the month#2, #3
    DayDDDay (01 to the last day of the month)
    HourHHHour (00 to 23)
    MinuteMIMinute (00 to 59)
    SecondSSSecond (00 to 59)#8
    Fractional secondFFFractional second#4, #5
    NN...NFractional second in p digits (p = N, where N is 1 to 6)#6
    OtherSpace ( )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

MONTH[Figure]JUNE
Month[Figure]June
month[Figure]june

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 characterFirst character
Upper caseLower case
Upper caseAll upper caseAll lower case
Lower caseUpper case in the first character onlyAll 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)

MonthAbbreviated nameName
1JANJANUARY
2FEBFEBRUARY
3MARMARCH
4APRAPRIL
5MAYMAY
6JUNJUNE
7JULJULY
8AUGAUGUST
9SEPSEPTEMBER
10OCTOCTOBER
11NOVNOVEMBER
12DECDECEMBER
#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 itemDatetime format elementMeaning
SecondSSSecond (00 to 61)

(4) Rules for datetime format elements

  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 elementCompatible character string
    YYYYCurrent year (e.g., '2002')
    YYLast two digits of the current year (e.g., '02')
    MMCurrent month (e.g., '08')
    MONCurrent month, abbreviated name (e.g., 'AUG')
    MONTHName of the current month (e.g., 'AUGUST')
    DDCurrent 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 functionRequired datetime items
    DATEYear, month, day
    TIMEHour, minute, second
    TIMESTAMP_FORMATYear, 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 elementVARCHAR_FORMATDATETIMETIMESTAMP_FORMAT
    DATE type#1TIME type#1TIMESTAMP type#1
    YYYYY#2Y#2
    (Current year)
    Y#2RY#5R
    YYY#2Y#2
    (Last 2 digits of current year)
    Y#2NNN
    MMY#3Y#3
    (Current month)
    Y#3R#3Y#3, #5R#3
    MONY#3Y#3
    (Abbr. name of current month)
    Y#3R#3Y#3, #5R#3
    MONTHY#3Y#3
    (Name of current month)
    Y#3R#3Y#3, #5R#3
    DDYY
    (Current day)
    YRY#5R
    HHY
    ('00')
    YYY#5RR
    MIY
    ('00')
    YYY#5RR
    SSY
    ('00')
    YYY#5RR
    FFY#4
    ('00')
    Y#4
    ('00')
    Y#4NNN
    NN...NY#4
    ('00...0')
    Y#4
    ('00...0')
    Y#4Y#5Y#5Y
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.