TO_CHAR Function (date_time)

Description

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.

Syntax

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'

Date/Time Format 1

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)
Note : MI represents the minute of hour.

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

Example

--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'