The TO_CHAR function converts the value of strings with TIME format (HH:MI:SS) or that of date/time type (TIME, TIMESTAMP, DATETIME) by Date/Time Format 1 and then return the value with the VARCHAR data type. If a format argument is not specified, it converts the value based by default format. If a format which is not corresponding to the given value, an error is returned.
TO_CHAR( date_time [, format [, date_lang_string_literal ]] )
date_time :
• date
• time
• timestamp
• datetime
• NULL
format :
• character strings (see Date/Time Format 1 )
• NULL
date_lang_string_literal : (see date_lang_string_literal)
• 'en_US'
• 'ko_KR'
Default Date/Time Format
Date/Time Type |
Default Output Format |
---|---|
DATE |
'MM/DD/YYYY' |
TIME |
'HH:MI:SS AM' |
TIMESTAMP |
'HH:MI:SS AM MM/DD/YYYY' |
DATETIME |
'HH:MI:SS.FF AM MM/DD/YYYY' |
Format Element |
Description |
---|---|
CC |
Century |
YYYY, YY |
Year with 4 numbers, Year with 2 numbers |
Q |
Quarter (1, 2, 3, 4; January - March = 1) |
MM |
Month (01-12; January = 01) |
MONTH |
Month in characters |
MON |
Abbreviated month name |
DD |
Day (1 - 31) |
DAY |
Day of the week in characters |
DY |
Abbreviated day of the week |
D or d |
Day of the week in numbers (1 - 7) |
AM or PM |
AM/PM |
A.M. or P.M. |
AM/PM with periods |
HH or HH12 |
Hour (1 -12) |
HH24 |
Hour (0 - 23) |
MI |
Minute (0 - 59) |
SS |
Second (0 - 59) |
FF |
Millsecond (0-999) |
- / , . ; : "text" |
Punctuation and quotation marks are represented as they are in the result |
Example of date_lang_string_literal
Format Element |
Date_lang_string_literal |
|
---|---|---|
'en_US' |
'ko_KR' |
|
MONTH |
JANUARY |
1월 |
MON |
JAN |
1 |
DAY |
MONDAY |
월요일 |
DY |
MON |
월 |
Month |
January |
1월 |
Mon |
Jan |
1 |
Day |
Monday |
월요일 |
Dy |
Mon |
월 |
month |
january |
1월 |
mon |
jan |
1 |
day |
monday |
월요일 |
Dy |
mon |
월 |
AM |
AM |
오전 |
Am |
Am |
오전 |
am |
am |
오전 |
A.M. |
A.M. |
오전 |
A.m. |
A.m. |
오전 |
a.m. |
a.m. |
오전 |
PM |
AM |
오전 |
Pm |
Am |
오전 |
pm |
am |
오전 |
P.M. |
A.M. |
오전 |
P.m. |
A.m. |
오전 |
p.m. |
a.m |
오전 |
The Number of Digits Format
Format Element |
Number of Digits |
---|---|
MONTH(Month, month) |
9 (ko_KR : 4) |
MON(Mon, mon) |
3 (ko_KR : 2) |
DAY(Day, day) |
9 (ko_KR : 6) |
DY(Dy, dy) |
3 (ko_KR : 2) |
HH12, HH24 |
2 |
"text" |
The length of the text |
Other formats |
Same as the length of the format |
--creating a table having date/time type columns
CREATE TABLE datetime_tbl(a TIME, b DATE, c TIMESTAMP, d DATETIME);
INSERT INTO datetime_tbl VALUES(SYSTIME, SYSDATE, SYSTIMESTAMP, SYSDATETIME);
--selecting a VARCHAR type string from the data in the specified format
SELECT TO_CHAR(b, 'DD, DY , MON, YYYY') FROM datetime_tbl;
to_char(b, 'DD, DY , MON, YYYY', 'en_US')
======================
'04, THU , FEB, 2010'
SELECT TO_CHAR(c, 'HH24:MI, DD, MONTH, YYYY') FROM datetime_tbl;
to_char(c, 'HH24:MI, DD, MONTH, YYYY', 'en_US')
======================
'16:50, 04, FEBRUARY , 2010'
SELECT TO_CHAR(c, 'HH24:MI:FF, DD, MONTH, YYYY') FROM datetime_tbl;
ERROR: Invalid format.
SELECT TO_CHAR(d, 'HH12:MI:SS:FF pm, YYYY-MM-DD-DAY') FROM datetime_tbl;
to_char(d, 'HH12:MI:SS:FF pm, YYYY-MM-DD-DAY', 'en_US')
======================
'04:50:11:624 pm, 2010-02-04-THURSDAY '
SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy');
to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy', 'en_US')
======================
'Sunday October 2009'