TO_CHAR Function (date_time)

Description

The TO_CHAR (date_time) function converts the value of date/time types (TIME, DATE, TIMESTAMP, DATETIME) to based on Date/Time Format 1 and then returns the value. The type of the return value is VARCHAR.

When the format argument is assigned, the date_time is output according to the specified language (see the Date/Time Format 1 table). At this time, the language specified to the intl_date_lang argument is applied. For example, when the language is set to "de_DE" and the format is "HH:MI:SS:AM", "08:46:53 PM" is output as "08:46:53 Nachm.". When the intl_date_lang value is not set, the language applied to the CUBRID_LANG environment variable is applied. When the format argument specified does not correspond to the given string, an error is returned.

When the format argument is omitted, the date_time is output as a string according to the default output format of the language set by intl_date_lang or CUBRID_LANG (see the following table Default output formats for date/time type by language).

Syntax

TO_CHAR( date_time [, format[, date_lang_string_literal ]] )

 

date_time :

• date

• time

• timestamp

• datetime

• NULL

 

format :

• character strings (see the table, Date/Time Format 1)

• NULL

 

date_lang_string_literal : (see the table, date_lang_string_literal)

• 'en_US'

• 'de_DE'

• 'es_ES'

• 'fr_FR'

• 'it_IT'

• 'ja_JP'

• 'km_KH'

• 'ko_KR'

• 'tr_TR'

• 'vi_VN'

• 'zh_CN'

• date_time: Specifies an expression that returns date-time type string. If the value is NULL, NULL is returned.
• format: Specifies a format of return value. If the value is NULL, NULL is returned.
• date_lang_string_literal: Specifies a language applied to a return value (see the table, date_lang_string_literal).

Note The CUBRID_DATE_LANG environment used in earlier version of CUBRID 9.0 is no longer supported.

Default Date/Time Output Format for Each Language

	DATE	TIME	TIMESTAMP	DATETIME
en_US	'MM/DD/YYYY'	'HH:MI:SS AM'	'HH:MI:SS AM MM/DD/YYYY'	'HH:MI:SS.FF AM MM/DD/YYYY'
de_DE	'DD.MM.YYYY'	'HH24:MI:SS'	'HH24:MI:SS DD.MM.YYYY'	'HH24:MI:SS.FF DD.MM.YYYY'
es_ES	'DD.MM.YYYY'	'HH24:MI:SS'	'HH24:MI:SS DD.MM.YYYY'	'HH24:MI:SS.FF DD.MM.YYYY'
fr_FR	'DD.MM.YYYY'	'HH24:MI:SS'	'HH24:MI:SS DD.MM.YYYY'	'HH24:MI:SS.FF DD.MM.YYYY'
it_IT	'DD.MM.YYYY'	'HH24:MI:SS'	'HH24:MI:SS DD.MM.YYYY'	'HH24:MI:SS.FF DD.MM.YYYY'
ja_JP	'YYYY/MM/DD'	'HH24:MI:SS'	'HH24:MI:SS YYYY/MM/DD'	'HH24:MI:SS.FF YYYY/MM/DD'
km_KH	'DD/MM/YYYY'	'HH24:MI:SS'	'HH24:MI:SS DD/MM/YYYY'	'HH24:MI:SS.FF DD/MM/YYYY '
ko_KR	'YYYY. MM. DD'	'HH24:MI:SS'	'HH24:MI:SS YYYY. MM. DD'	'HH24:MI:SS.FF YYYY. MM. DD'
tr_TR	'DD.MM.YYYY'	'HH24:MI:SS'	'HH24:MI:SS DD.MM.YYYY'	'HH24:MI:SS.FF DD.MM.YYYY'
vi_VN	'DD/MM/YYYY'	'HH24:MI:SS'	'HH24:MI:SS DD/MM/YYYY'	'HH24:MI:SS.FF DD/MM/YYYY'
zh_CN	'YYYY-MM-DD'	'HH24:MI:SS'	'HH24:MI:SS YYYY-MM-DD'	'HH24:MI:SS.FF YYYY-MM-DD'

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	PM	오후
Pm	Pm	오후
pm	pm	오후
P.M.	P.M.	오후
P.m.	P.m.	오후
p.m.	p.m	오후

Example of Format Digits of Return Value

Format Element	Digits
	en_US	ko_KR
MONTH(Month, month)	9	4
MON(Mon, mon)	3	2
DAY(Day, day)	9	6
DY(Dy, dy)	3	2
HH12, HH24	2	2
"text"	The length of the text	The length of the text
Other formats	Same as the length of the format	Same as the length of the format

Example

The following example shows execution of the database by setting the environment variable CUBRID_LANG to "en_US.iso88591".

--set the initial locale as en_US.iso88591

export CUBRID_LANG=en_US.iso88591

 

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

======================

  '04, THU , FEB, 2010'

 

SELECT TO_CHAR(c, 'HH24:MI, DD, MONTH, YYYY') FROM datetime_tbl;

 to_char(c, 'HH24:MI, DD, MONTH, YYYY')

======================

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

======================

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

======================

  'Sunday October 2009'

The following example shows an additional language parameter given to the TO_CHAR function in the database created above.

When the charset is ISO-8859-1, setting the language parameter of the TO_CHAR function to "tr_TR" or "ko_KR" is allowed, but the other languages are not allowed. To use all languages by setting the language parameter of TO_CHAR, the charset should be UTF-8 when the database is created.

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','ko_KR');

   to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy', 'ko_KR')

======================

  'Iryoil    10wol 2009'

 

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','tr_TR');

   to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy', 'tr_TR')

======================

  'Pazar     Ekim    2009'

Remark

When only the language is set to "en_US" (the initial value of CUBRID_LANG at installation of CUBRID) in the locale of the CUBRID_LANG environment variable and charset after "." is omitted, the charset is set to ISO-8859-1 (.iso88591). That is, setting the locale value of CUBRID_LANG to "en_US" is identical with setting to "en_US.iso88591".

In the function that interprets the month/day in characters and AM/PM differently by language, if the charset is ISO-8859-1, the language can be changed to "ko_KR" or "tr_TR" only by using the intl_date_lang or CUBRID_LANG (environment variable) except "en_US" (see the above example). If the charset is UTF-8, the language can be changed to any language supported by CUBRID. By setting the intl_date_lang system parameter or by specifying the language parameter of the TO_CHAR function, the language can be changed to one of all the languages supported by CUBRID (see date_lang_string_literal of "Syntax" above). For a list of functions that interpret the date/time differently by language, see the description of the intl_date_lang system parameter.

-- change date locale as "de_DE" and run above query.

-- This case is failed because database locale, 'en_US'’s charset is ISO-8859-1, and 'de_DE' only supports UTF-8 charset.

 

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','de_DE');

 

In line 1, column 16,

 

ERROR: before ' , 'Day Month yyyy','de_DE'); '

Locales for language 'de_DE' are not available with charset 'iso8859-1'.

The following example shows how to set the language parameter of the TO_CHAR function to "de_DE" on the database created by setting the CUBRID_LANG to "en_US.utf8". You can see that the execution has successfully completed.

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','de_DE');

 

   to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy', 'de_DE')

======================

  'Sonntag   Oktober 2009'