The following operations require the specification of a datetime format:
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 |
MONTHJUNE
MonthJune
monthjune
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 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 |
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) |
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 |
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 |