The following operations require the specification of a datetime format:
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 |
MONTHJUNE
MonthJune
monthjune
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 |
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 |
Datetime item | Datetime format element | Meaning |
---|---|---|
Second | SS | Second (00 to 61) |
Example
VARCHAR_FORMAT(DATE('2002-01-01'),'YYYY-MM-DD HH:MI')
-> '2002-01-01 00:00'
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) |
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 |
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 |