Data Type Casting Functions and Operators ¶

CAST ¶

CAST(cast_operand AS cast_target)¶

The CAST operator can be used to explicitly cast one data type to another in the SELECT statement. A query list or a value expression in the WHERE clause can be cast to another data type.

Parameters:

cast_operand – Declares the value to cast to a different data type.
cast_target – Specifies the type to cast to.

Return type:

cast_target

Depending on the situation, data type can be automatically converted without using the CAST operator. For details, see Implicit Type Conversion.

See CASTing a String to Date/Time Type regarding to convert the string of date/time type into date/time type.

The following table shows a summary of explicit type conversions (casts) using the CAST operator in CUBRID.

From \ To

EN

AN

VC

FC

VB

FB

ENUM

BLOB

CLOB

D

T

UT

UTZ

DT

DTZ

S

MS

SQ

EN

Yes

No

Yes

No

AN

Yes

No

Yes

No

VC

Yes

Yes(*)

Yes

No

FC

Yes

Yes(*)

Yes

No

VB

No

Yes

No

Yes

No

FB

No

Yes

No

Yes

No

ENUM

No

Yes

No

Yes

No

BLOB

No

Yes

No

CLOB

No

Yes

No

Yes

No

Yes

No

D

No

Yes

No

Yes

No

Yes

No

T

No

Yes

No

Yes

No

UT

No

Yes

No

Yes

No

UTZ

No

Yes

No

Yes

No

DT

No

Yes

No

Yes

No

DTZ

No

Yes

No

Yes

No

S

No

Yes

MS

No

Yes

SQ

No

Yes

(*): The CAST operation is allowed only when the value expression and the data type to be cast have the same character set.

Data Type Key
- EN: Exact numeric data type (INTEGER, SMALLINT, BIGINT, NUMERIC, DECIMAL)
- AN: Approximate numeric data type (FLOAT/REAL, DOUBLE)
- VC: Variable-length character string (VARCHAR (n))
- FC: Fixed-length character string (CHAR (n))
- VB: Variable-length bit string (BIT VARYING (n))
- FB: Fixed-length bit string (BIT (n))
- ENUM: ENUM type
- BLOB: Binary data that is stored outside DB
- CLOB: String data that is stored inside DB
- D: DATE
- T: TIME
- DT: DATETIME
- DTZ: DATETIME WITH TIME ZONE and DATETIME WITH LOCAL TIME ZONE data types
- UT: TIMESTAMP
- UTZ: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types
- S: SET
- MS: MULTISET
- SQ: LIST (= SEQUENCE)

--operation after casting character as INT type returns 2
SELECT (1+CAST ('1' AS INT));

--cannot cast the string which is out of range as SMALLINT
SELECT (1+CAST('1234567890' AS SMALLINT));

ERROR: Cannot coerce value of domain "character" to domain "smallint".

--operation after casting returns 1+1234567890
SELECT (1+CAST('1234567890' AS INT));

1234567891

--'1234.567890' is casted to 1235 after rounding up
SELECT (1+CAST('1234.567890' AS INT));

--'1234.567890' is casted to string containing only first 5 letters.
SELECT (CAST('1234.567890' AS CHAR(5)));

'1234.'

--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS CHAR(5)));

ERROR: Cannot coerce value of domain "numeric" to domain "character".

--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS CHAR(11)));

'1234.567890'

--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS VARCHAR));

'1234.567890'

--string can be casted to time/date types only when its literal is correctly specified
SELECT (CAST('2008-12-25 10:30:20' AS TIMESTAMP));

10:30:20 AM 12/25/2008

SELECT (CAST('10:30:20' AS TIME));

10:30:20 AM

--string can be casted to TIME type when its literal is same as TIME's.
SELECT (CAST('2008-12-25 10:30:20' AS TIME));

10:30:20 AM

--string can be casted to TIME type after specifying its type of the string
SELECT (CAST(TIMESTAMP'2008-12-25 10:30:20' AS TIME));

10:30:20 AM

SELECT CAST('abcde' AS BLOB);

file:/home1/user1/db/tdb/lob/ces_743/ces_temp.00001283232024309172_1342

SELECT CAST(B'11010000' as varchar(10));

'd0'

SELECT CAST('1A' AS BLOB);

X'1a00'

--numbers can be casted to TIMESTAMP type
SELECT CAST (1 AS TIMESTAMP), CAST (1.2F AS TIMESTAMP);

09:00:01 AM 01/01/1970     09:00:01 AM 01/01/1970

--numbers cannot be casted to DATETIME type
SELECT CAST (1 AS DATETIME);

Cannot coerce 1 to type datetime

--TIMESTAMP cannot be casted to numbers
SELECT CAST (TIMESTAMP'09:00:01 AM 01/01/1970' AS INT)

Cannot coerce timestamp '09:00:01 AM 01/01/1970' to type integer.

Note

CAST is allowed only between data types having the same character set.
If you cast an approximate data type(FLOAT, DOUBLE) to integer type, the number is rounded to zero decimal places.
If you cast an exact numeric data type(NUMERIC) to integer type, the number is rounded to zero decimal places.
If you cast a numeric data type to string character type, it should be longer than the length of significant figures + decimal point. An error occurs otherwise.
If you cast a character string type A to a character string type B, B should be longer than the A. The end of character string is truncated otherwise.
If you cast a character string type A to a date-time date type B, it is converted only when literal of A and B type match one another. An error occurs otherwise.
You must explicitly do type casting for numeric data stored in a character string so that an arithmetic operation can be performed.

The DATE_FORMAT function converts the value of date/time data type which include a date to specified date/time format and then return the value with the VARCHAR data type. For the format parameter to assign, refer to Date/Time Format 2 table of the DATE_FORMAT(). The Date/Time Format 2 table is used in DATE_FORMAT(), TIME_FORMAT(), and STR_TO_DATE() functions.

Parameters:

date – A value of DATE, TIMESTAMP, DATETIME, DATETIMETZ, DATETIMELTZ, TIMESTAMPTZ, or TIMESTAMP.
format – Specifies the output format. The format specifier starting with ‘%’ is used.

Return type:

STRING

When the format argument is assigned, the string is interpreted according to the specified language. At that time, the language specified as the intl_date_lang system parameter is applied. If the intl_date_lang value is not set, the language specified when creating DB is applied.

For example, when the language is “de_DE” and the format is “%d %M %Y”, the string “3 Oktober 2009” is interpreted as the DATE type of “2009-10-03”. When the specified format argument does not correspond to the given string, an error is returned.

In the following Date/Time Format 2 table, the month/day, date, and AM/PM in characters are different by language.

Date/Time Format 2

format Value	Meaning
%a	Weekday, English abbreviation (Sun, … , Sat)
%b	Month, English abbreviation (Jan, … , Dec)
%c	Month (1, … , 12)
%D	Day of the month, English ordinal number (1st, 2nd, 3rd, …)
%d	Day of the month, two-digit number (01, … , 31)
%e	Day of the month (1, … , 31)
%f	Microseconds, three-digit number (000, … , 999)
%H	Hour, 24-hour based, number with at least two–digit (00, … , 23, … , 100, … )
%h	Hour, 12-hour based two-digit number (01, … , 12)
%I	Hour, 12-hour based two-digit number (01, … , 12)
%i	Minutes, two-digit number (00, … , 59)
%j	Day of year, three-digit number (001, … , 366)
%k	Hour, 24-hour based, number with at least one-digit (0, … , 23, … , 100, … )
%l	Hour, 12-hour based (1, … , 12)
%M	Month, English string (January, … , December)
%m	Month, two-digit number (01, … , 12)
%p	AM or PM
%r	Time, 12-hour based, hour:minute:second (hh:mi:ss AM or hh:mi:ss PM)
%S	Seconds, two-digit number (00, … , 59)
%s	Seconds, two-digit number (00, … , 59)
%T	Time, 24-hour based, hour:minute:second (hh:mi:ss)
%U	Week, two-digit number, week number of the year with Sunday being the first day Week (00, … , 53)
%u	Week, two-digit number, week number of the year with Monday being the first day (00, … , 53)
%V	Week, two-digit number, week number of the year with Sunday being the first day Week (00, … , 53) (Available to use in combination with %X)
%v	Week, two-digit number, week number of the year with Monday being the first day (00, … , 53) (Available to use in combination with %x)
%W	Weekday, English string (Sunday, … , Saturday)
%w	Day of the week, number index (0=Sunday, … , 6=Saturday)
%X	Year, four-digit number calculated as the week number with Sunday being the first day of the week (0000, … , 9999) (Available to use in combination with %V)
%x	Year, four-digit number calculated as the week number with Monday being the first day of the week (0000, … , 9999) (Available to use in combination with %v)
%Y	Year, four-digit number (0001, … , 9999)
%y	Year, two-digit number (00, 01, … , 99)
%%	Output the special character “%” as a string
%x	Output an arbitrary character x as a string out of English letters that are not used as format specifiers.
%TZR	Time zone region information. e.g. US/Pacific.
%TZD	Daylight saving information. e.g. KST, KT, EET
%TZH	Timezone hour offset. e.g. +09, -09
%TZM	Timezone minute offset. e.g. +00, +30

Note

%TZR, %TZD, %TZH, %TZM can be used only in timezone types.

Note

A format specifying a number after TZD

See A format specifying a number after TZD.

The following example shows the case when the system parameter intl_date_lang is “en_US”.

SELECT DATE_FORMAT(datetime'2009-10-04 22:23:00', '%W %M %Y');

'Sunday October 2009'

SELECT DATE_FORMAT(datetime'2007-10-04 22:23:00', '%H:%i:%s');

'22:23:00'

SELECT DATE_FORMAT(datetime'1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

'4th 00 Thu 04 10 Oct 277'

SELECT DATE_FORMAT(date'1999-01-01', '%X %V');

'1998 52'

The following example shows the case when the system parameter intl_date_lang is “de_DE”.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT DATE_FORMAT(datetime'2009-10-04 22:23:00', '%W %M %Y');

'Sonntag Oktober 2009'

SELECT DATE_FORMAT(datetime'2007-10-04 22:23:00', '%H:%i:%s %p');

'22:23:00 Nachm.'

SELECT DATE_FORMAT(datetime'1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

'4 00 Do. 04 10 Okt 277'

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is “ko_KR” and “tr_TR” except “en_US”. If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

The following example outputs the value of DATETIMETZ type which includes timezone information as the desired format.

SELECT DATE_FORMAT(datetimetz'2012-02-02 10:10:10 Europe/Zurich CET', '%TZR %TZD %TZH %TZM');

::: ‘Europe/Zurich CET 01 00’

FORMAT ¶

FORMAT(x, dec)¶

The FORMAT function displays the number x by using digit grouping symbol as a thousands separator, so that its format becomes ‘#,###,###.#####’ and performs rounding after the decimal point symbol to express as many as dec digits after it. The return value is a VARCHAR type.

Parameters:

x – An expression that returns a numeric value
dec – the number of digits of fractional parts

Return type:

STRING

Thousands separator symbol and decimal point symbol is output in the format according to the specified language. The language is specified by the intl_number_lang system parameter. If the value of intl_number_lang is not set, the language specified when creating DB is applied.

For example, when the language is one of the European languages, such as “de_DE” or “fr_FR”, “.” is interpreted as the thousands separator and “,” as the decimal point symbol (see Default output of number by language of the TO_CHAR()).

The following example shows command execution by setting the value of the intl_number_lang system parameter to “en_US”.

SET SYSTEM PARAMETERS 'intl_number_lang="en_US"';
SELECT FORMAT(12000.123456,3), FORMAT(12000.123456,0);

'12,000.123'          '12,000'

The following example shows command execution on the database by setting the value of the intl_number_lang system parameter to “de_DE”. In the number output format of most European countries, such as Germany and France, “.” is the cipher identifier and “,” is the decimal point symbol.

SET SYSTEM PARAMETERS 'intl_number_lang="de_DE"';
SELECT FORMAT(12000.123456,3), FORMAT(12000.123456,0);

'12.000,123'          '12.000'

STR_TO_DATE ¶

STR_TO_DATE(string, format)¶

The STR_TO_DATE function converts the given character string to a date/time value by interpreting it according to the specified format and operates in the opposite way to the DATE_FORMAT() function. The return value is determined by the date/time part included in the character string.

Parameters:

string – String.
format – Specifies the format to interpret the character string. You should use character strings including % for the format specifiers. See Date/Time Format 2 table of DATE_FORMAT() function.

Return type:

DATETIME, DATE, TIME, DATETIMETZ

For the format argument to assign, see Date/Time Format 2 table of the DATE_FORMAT().

If string is invalid date/time value or format is invalid, it returns an error.

When the format argument is assigned, the string is interpreted according to the specified language. At that time, the language specified as the intl_date_lang system parameter is applied. If the intl_date_lang value is not set, the language specified when creating DB is applied.

For example, when the language is “de_DE” and the format is “%d %M %Y”, the string “3 Oktober 2009” is interpreted as the DATE type of “2009-10-03”. If the format argument does not correspond to the given string, an error is returned.

0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, the value of DATE or DATETIME type that has 0 for every date and time value is returned as an exception. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property. For more information about zeroDateTimeBehavior, please see Configuration Connection.

The following example shows the case when the system parameter intl_date_lang is “en_US”.

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

05/01/2013

SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');

05/01/2013

SELECT STR_TO_DATE('13:30:17','%H:%i');

01:30:00 PM

SELECT STR_TO_DATE('09:30:17 PM','%r');

09:30:17 PM

SELECT STR_TO_DATE('0,0,0000','%d,%m,%Y');

00/00/0000

The following example shows the case when the system parameter intl_date_lang is “de_DE”. The German Oktober is interpreted to 10.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT STR_TO_DATE('3 Oktober 2009', '%d %M %Y');

10/03/2009

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is “ko_KR” and “tr_TR” except “en_US”. If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

The following example shows which converts a date/time string with timezone information into DATETIMETZ type value.

SELECT STR_TO_DATE('2001-10-11 02:03:04 AM Europe/Bucharest EEST', '%Y-%m-%d %h:%i:%s %p %TZR %TZD');

02:03:04.000 AM 10/11/2001 Europe/Bucharest EEST

TIME_FORMAT ¶

TIME_FORMAT(time, format)¶

The TIME_FORMAT function converts the date/time data type value including time value into a string of specified date/time format, and returns the value with the VARCHAR data type.

Parameters:

time – A value of a type with time. (TIME, TIMESTAMP, DATETIME, TIMESTAMPTZ or DATETIMETZ)
format – Specifies the output format. Use a string that contains ‘%’ as a specifier. See the table Date/Time Format 2 of DATE_FORMAT() function.

Return type:

STRING

When the format argument is assigned, the time is output according to the specified language. At this time, the language specified as the intl_date_lang system parameter is applied. If intl_date_lang system parameter is not set, the language specified when creating DB is applied.

For example, when the language is set to “de_DE” and the format is “%h:%i:%s %p”, “08:46:53 PM” is output as “08:46:53 Nachm.”. When the format argument specified does not correspond to the given string, an error is returned.

The following example shows the case when the system parameter intl_date_lang is “en_US”.

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';
SELECT TIME_FORMAT(time'22:23:00', '%H %i %s');

'22 23 00'

SELECT TIME_FORMAT(time'23:59:00', '%H %h %i %s %f');

'23 11 59 00 000'

SELECT SYSTIME, TIME_FORMAT(SYSTIME, '%p');

08:46:53 PM  'PM'

The following example shows the case when the system parameter intl_date_lang is “de_DE”.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT SYSTIME, TIME_FORMAT(SYSTIME, '%p');

08:46:53 PM  'Nachm.'

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is “ko_KR” and “tr_TR” except “en_US”. If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

The following outputs the value with a timezone information into a specified format string.

SELECT TIME_FORMAT(datetimetz'2001-10-11 02:03:04 AM Europe/Bucharest EEST', '%h:%i:%s %p %TZR %TZD');

'02:03:04 AM Europe/Bucharest EEST'

TO_CHAR(date_time)¶

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

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

Parameters:

date_time – A value of date/time type. (TIME, DATE, TIMESTAMP, DATETIME, DATETIMETZ, DATETIMELTZ, TIMESTAMPTZ, TIMESTAMPLTZ)
format – A format of return value.
date_lang_string_literal – Specifies a language applied to a return value.

Return type:

STRING

When the format argument is specified, the date_time is output according to the specified language (see the Date/Time Format 1 table). A language is defined by the date_lang_string_literal. If date_lang_string_literal is omitted, the language specified by the intl_date_lang parameter is applied; if the value of intl_date_lang is not specified, the language specified when creating DB 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 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 “en_US”(see the following table Default Date/Time Output Format for Each Language).

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

LANG	DATE	TIME	TIMESTAMP	DATETIME	TIMESTAMP WITH TIME ZONE	DATETIME WITH TIME ZONE
en_US	‘MM/DD/YYYY’	‘HH:MI:SS AM’	‘HH:MI:SS AM MM/DD/YYYY’	‘HH:MI:SS.FF AM MM/DD/YYYY’	‘HH:MI:SS AM MM/DD/YYYY TZR’	‘HH:MI:SS.FF AM MM/DD/YYYY TZR’
de_DE	‘DD.MM.YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD.MM.YYYY’	‘HH24:MI:SS.FF DD.MM.YYYY’	‘HH24:MI:SS DD.MM.YYYY TZR’	‘HH24:MI:SS.FF DD.MM.YYYY TZR’
es_ES	‘DD.MM.YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD.MM.YYYY’	‘HH24:MI:SS.FF DD.MM.YYYY’	‘HH24:MI:SS DD/MM/YYYY TZR’	‘HH24:MI:SS.FF DD/MM/YYYY TZR’
fr_FR	‘DD.MM.YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD.MM.YYYY’	‘HH24:MI:SS.FF DD.MM.YYYY’	‘HH24:MI:SS DD/MM/YYYY TZR’	‘HH24:MI:SS.FF DD/MM/YYYY TZR’
it_IT	‘DD.MM.YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD.MM.YYYY’	‘HH24:MI:SS.FF DD.MM.YYYY’	‘HH24:MI:SS DD/MM/YYYY TZR’	‘HH24:MI:SS.FF DD/MM/YYYY TZR’
ja_JP	‘YYYY/MM/DD’	‘HH24:MI:SS’	‘HH24:MI:SS YYYY/MM/DD’	‘HH24:MI:SS.FF YYYY/MM/DD’	‘HH24:MI:SS YYYY/MM/DD TZR’	‘HH24:MI:SS.FF YYYY/MM/DD TZR’
km_KH	‘DD/MM/YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD/MM/YYYY’	‘HH24:MI:SS.FF DD/MM/YYYY’	‘HH24:MI:SS DD/MM/YYYY TZR’	‘HH24:MI:SS.FF DD/MM/YYYY TZR’
ko_KR	‘YYYY.MM.DD’	‘HH24:MI:SS’	‘HH24:MI:SS YYYY.MM.DD’	‘HH24:MI:SS.FF YYYY.MM.DD’	‘HH24:MI:SS YYYY.MM.DD TZR’	‘HH24:MI:SS.FF YYYY.MM.DD TZR’
tr_TR	‘DD.MM.YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD.MM.YYYY’	‘HH24:MI:SS.FF DD.MM.YYYY’	‘HH24:MI:SS DD.MM.YYYY TZR’	‘HH24:MI:SS.FF DD.MM.YYYY TZR’
vi_VN	‘DD/MM/YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD/MM/YYYY’	‘HH24:MI:SS.FF DD/MM/YYYY’	‘HH24:MI:SS DD/MM/YYYY TZR’	‘HH24:MI:SS.FF DD/MM/YYYY TZR’
zh_CN	‘YYYY-MM-DD’	‘HH24:MI:SS’	‘HH24:MI:SS YYYY-MM-DD’	‘HH24:MI:SS.FF YYYY-MM-DD’	‘HH24:MI:SS YYYY-MM-DD TZR’	‘HH24:MI:SS.FF YYYY-MM-DD TZR’
ro_RO	‘DD.MM.YYYY’	‘HH24:MI:SS’	‘HH24:MI:SS DD.MM.YYYY’	‘HH24:MI:SS.FF DD.MM.YYYY’	‘HH24:MI:SS DD.MM.YYYY TZR’	‘HH24:MI:SS.FF DD.MM.YYYY TZR’

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	Millisecond (0-999)
- / , . ; : “text”	Punctuation and quotation marks are represented as they are in the result
TZD	Daylight saving information. e.g. KST, KT, EET
TZH	Timezone hour offset. e.g. +09, -09
TZM	Timezone minute offset. e.g. +00, +30

Note

TZR, TZD, TZH, TZM can be used only in timezone types.

Note

A format to specify a number after “TZD”

A number can be added after “TZD”. This format is from TZD2 to TZD11; When you use a general character as a separator of a string, this format can be used.

SELECT STR_TO_DATE('09:30:17 20140307XEESTXEurope/Bucharest','%h:%i:%s %Y%d%mX%TZD4X%TZR');
09:30:17.000 AM 07/03/2014 Europe/Bucharest EEST
When you use a general character, ‘X’, as a separator to separate each value, TZD value’s string length is variable; therefore, it is confused to separate TZD value and a separator. In this case, TZD values’ length should be specified.

Example of date_lang_string_literal

Format Element	date_lang_string_literal
Format Element	‘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	en_US Digits \| ko_KR Digits
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

The following example shows the query executed by setting the language and charset to “en_US.iso88591”.

-- create database testdb 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;

'20, TUE , AUG, 2013'

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

'17:00, 20, AUGUST   , 2013'

SELECT TO_CHAR(d, 'HH12:MI:SS:FF pm, YYYY-MM-DD-DAY') FROM datetime_tbl;

'05:00:58:358 pm, 2013-08-20-TUESDAY  '

SELECT 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 when creating DB should be UTF-8.

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

'Pazar     Ekim    2009'

Note

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 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 the below 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');
```
```
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” when you created DB with the locale “en_US.utf8”.
```
SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','de_DE');
```
```
'Sonntag   Oktober 2009'
```
If the first argument is zerodate and the second argument has a literal like ‘Month’, ‘Day’, then TO_CHAR function returns NULL.
```
SELECT TO_CHAR(timestamp '0000-00-00 00:00:00', 'Month Day YYYY');
```
```
NULL
```

The following is an example to output date/time type with timezone in TO_CHAR function.

If you don’t define a format, it outputs as the following format

SELECT TO_CHAR(datetimetz'2001-10-11 02:03:04 AM Europe/Bucharest EEST');

'02:03:04.000 AM 10/11/2001 Europe/Bucharest EEST'

If you define a format, it outputs as the defined format.

SELECT TO_CHAR(datetimetz'2001-10-11 02:03:04 AM Europe/Bucharest EEST', 'MM/DD/YYYY HH24:MI TZR TZD TZH TZM');

'10/11/2001 02:03 Europe/Bucharest EEST +03 +00'

TO_CHAR(number)¶

TO_CHAR(number[, format[, number_lang_string_literal]])¶

The TO_CHAR function converts a numeric data type to a character string according to Number Format and returns it. The type of the return value is VARCHAR .

Parameters:

number – Specifies an expression that returns numeric data type string. If the input value is NULL, NULL is returned. If the input value is character type, the character itself is returned.
format – Specifies a format of return value. If format is not specified, all significant figures are returned as character string by default. If the value is NULL, NULL is returned.
number_lang_string_literal – Specifies the language to be applied to the input value.

Return type:

STRING

If format argument is specified, number is converted into a character string according to a specified language. At this time, the language is defined by the number_lang_string_literal argument. If number_lang_string_literal is omitted, the language specified by intl_number_lang system parameter is applied; if intl_number_lang is not set, the language specified when creating DB is applied.

For example, if the language is one of the European languages, such as “de_DE” or “fr_FR”, “.” is printed out as a thousands separator and “,” is printed out as a decimal point. If the format argument does not correspond to the given string, the function returns an error.

If format argument is omitted, number is converted into a character string according to the default format of a specified language(see the table Default Output of Number for Each Language).

Number Format

Format Element	Example	Description
9	9999	The number of 9’s represents the number of significant figures to be returned. If the number of significant figures specified in the format is not sufficient, only the decimal part is rounded. If it is less than the number of digits in an integer, # is outputted. If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with space characters and the decimal part is filled with 0.
0	0999	If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with 0, not space characters before the value is returned.
S	S9999	Outputs the negative/positive sign in the specified position. These signs can be used only at the beginning of character string.
C	C9999	Returns the ISO currency code at the specified position.
, (comma)	9,999	Returns a comma (“,”) at the specified position. Multiple commas are allowed in the format.
. (decimal point)	9.999	Returns a decimal point (“.”) which distinguishes between a decimal and an at the specified position. Only one decimal point is allowed in the format. see the table, Default Output of Number for Each Language
EEEE	9.99EEEE	Returns a scientific notation number.

Default Output of Number for Each Language

Language	Locale	Number of Digits	Decimal Symbol	Example of Number Usage
English	en_US	,(comma)	.(period)	123,456,789.012
German	de_DE	.(period)	,(comma)	123.456.789,012
Spanish	es_ES	.(period)	,(comma)	123.456.789,012
French	fr_FR	.(period)	,(comma)	123.456.789,012
Italian	it_IT	.(period)	,(comma)	123.456.789,012
Japanese	ja_JP	,(comma)	.(period)	123,456,789.012
Cambodian	km_KH	.(period)	,(comma)	123.456.789,012
Korean	ko_KR	,(comma)	.(period)	123,456,789.012
Turkish	tr_TR	.(period)	,(comma)	123.456.789,012
Vietnamese	vi_VN	.(period)	,(comma)	123.456.789,012
Chinese	zh_CN	,(comma)	.(period)	123,456,789.012
Romanian	ro_RO	.(period)	,(comma)	123.456.789,012

The following example shows execution of the database by the locale specified when creating DB to “en_US.utf8”.

--selecting a string casted from a number in the specified format
SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999');

' +12345'             '+012345'

SELECT TO_CHAR(1234567,'9,999,999,999');

'    1,234,567'

SELECT TO_CHAR(1234567,'9.999.999.999');

'##############'

SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999.99999'), TO_CHAR(123.4567,'99999.999');

'##'                  '123.45670'           '  123.457'

The following example shows command execution by setting the value of the intl_number_lang system parameter to “de_DE”. In the number output format of most European countries such as Germany and France, “.” is the cipher identifier and “,” is the decimal point symbol.

SET SYSTEM PARAMETERS 'intl_number_lang="de_DE"';

--selecting a string casted from a number in the specified format
SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999');

' +12345'             '+012345'

SELECT TO_CHAR(1234567,'9,999,999,999');

'##############'

SELECT TO_CHAR(1234567,'9.999.999.999');

'    1.234.567'

SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999,99999'), TO_CHAR(123.4567,'99999,999');

'##'                  '123,45670'           '  123,457'

SELECT TO_CHAR(123.4567,'99','en_US'), TO_CHAR(123.4567,'999.99999','en_US'), TO_CHAR(123.4567,'99999.999','en_US');

'##'                  '123.45670'           '  123.457'

SELECT TO_CHAR(1.234567,'99.999EEEE','en_US'), TO_CHAR(1.234567,'99,999EEEE','de_DE'), to_char(123.4567);

'1.235E+00'           '1,235E+00'           '123,4567'

TO_DATE ¶

TO_DATE(string[, format[, date_lang_string_literal]])¶

The TO_DATE function interprets a character string based on the date format given as an argument, converts it to a DATE type value, and returns it. For the format, see Date/Time Format 1.

Parameters:

string – A character string
format – Specifies a format of return value to be converted as DATE type. See Date/Time Format 1. If the value is NULL, NULL is returned.
date_lang_string_literal – Specifies the language for the input value to be applied.

Return type:

DATE

When the format argument is specified, the string is interpreted according to the specified language. At this time, the language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is “de_DE” and string is “12.mai.2012”, and format is “DD.mon.YYYY”, it is interpreted as May 12th, 2012.When the format parameter specified does not correspond to the given string, an error is returned.

When the format argument is omitted, string is interpreted as the CUBRID default format (refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is “de_DE”, the acceptable strings for DATE type are “MM/DD/YYYY”, CUBRID default format and “DD.MM.YYYY”, “de_DE” default format.

The following example shows the query executed by the locale specified when creating DB to “en_US.utf8”.

--selecting a date type value casted from a string in the specified format

SELECT TO_DATE('12/25/2008');

12/25/2008

SELECT TO_DATE('25/12/2008', 'DD/MM/YYYY');

12/25/2008

SELECT TO_DATE('081225', 'YYMMDD');

12/25/2008

SELECT TO_DATE('2008-12-25', 'YYYY-MM-DD');

12/25/2008

The following example shows the query executed when the system parameter intl_date_lang is “de_DE”.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_DATE('25.12.2012');

12/25/2012

SELECT TO_DATE('12/mai/2012','dd/mon/yyyy', 'de_DE');

05/12/2012

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is “ko_KR” and “tr_TR” except “en_US”. If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

TO_DATETIME ¶

TO_DATETIME(string[, format[, date_lang_string_literal]])¶

The TO_DATETIME function interprets a character string based on the date-time format given as an argument, converts it to a DATETIME type value, and returns it. For the format, see Date/Time Format 1.

Parameters:

string – A character string
format – Specifies a format of return value to be converted as DATETIME type. See the table, Date/Time Format 1. If the value is NULL, NULL is returned.
date_lang_string_literal – Specifies the language for the input value to be applied.

Return type:

DATETIME

When the format argument is specified, the string is interpreted according to the specified language.

For example, when a language is “de_DE” and string is “12/mai/2012 12:10:00 Nachm.” and format is “DD/MON/YYYY HH:MI:SS AM”, it is interpreted as May 12th, 2012 12:10:00 PM. At this time, the language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language is specified by the language specified when creating DB. When the format parameter specified does not correspond to the given string, an error is returned.

When the format argument is omitted, string is interpreted as the CUBRID default format (refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is “de_DE”, the acceptable strings for DATETIME type are “HH:MI:SS.FF AM MM/DD/YYYY”, CUBRID default format and “HH24:MI:SS.FF DD.MM.YYYY”, “de_DE” default format.

Note

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

The following example shows execution of the database by setting the environment variable CUBRID_CHARSET to “en_US”.

--selecting a datetime type value casted from a string in the specified format

SELECT TO_DATETIME('13:10:30 12/25/2008');

01:10:30.000 PM 12/25/2008

SELECT TO_DATETIME('08-Dec-25 13:10:30.999', 'YY-Mon-DD HH24:MI:SS.FF');

01:10:30.999 PM 12/25/2008

SELECT TO_DATETIME('DATE: 12-25-2008 TIME: 13:10:30.999', '"DATE:" MM-DD-YYYY "TIME:" HH24:MI:SS.FF');

01:10:30.999 PM 12/25/2008

The following example shows the case when the system parameter intl_date_lang is “de_DE”.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_DATETIME('13:10:30.999 25.12.2012');

01:10:30.999 PM 12/25/2012

SELECT TO_DATETIME('12/mai/2012 12:10:00 Nachm.','DD/MON/YYYY HH:MI:SS AM', 'de_DE');

12:10:00.000 PM 05/12/2012

Note

When the charset is ISO-8859-1, the language that can be changed in TO_DATETIME function is “ko_KR” and “tr_TR” except “en_US”. If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR().

TO_DATETIME_TZ ¶

TO_DATETIME_TZ(string[, format[, date_lang_string_literal]])¶

TO_DATETIME_TZ function is the same as TO_DATETIME() function except that this function can include a timezone information on this input string.

Return type:: DATETIMETZ

SELECT TO_DATETIME_TZ('13:10:30 Asia/Seoul 12/25/2008', 'HH24:MI:SS TZR MM/DD/YYYY');

01:10:30.000 PM 12/25/2008 Asia/Seoul

TO_NUMBER ¶

TO_NUMBER(string[, format])¶

The TO_NUMBER function interprets a character string based on the number format given as an argument, converts it to a NUMERIC type value, and returns it.

Parameters:

string – Specifies an expression that returns character string. If the value is NULL, NULL is returned.
format – Specifies a format of return value to be converted as NUMBER type. See Number Format. If it is omitted, NUMERIC(38,0) value is returned.

Return type:

NUMERIC

When the format argument is assigned, the string is interpreted according to the specified language. At this time, the language is specified by the intl_number_lang system parameter. If the value of intl_number_lang is not set, the language specified when creating DB is applied.

For example, when the language is one of the European languages, such as “de_DE” and “fr_FR”, “.” is interpreted as the cipher identifier and “,” as the decimal point symbol. When the format parameter specified does not correspond to the given string, an error is returned.

If the format argument is omitted, string is interpreted according to the default output format set by intl_number_lang (see Default Output of Number for Each Language). When the intl_number_lang is not set, the language specified when creating DB is applied.

The following example shows execution of the database by setting the value of system parameter intl_number_lang as “en_US”.

SET SYSTEM PARAMETERS 'intl_number_lang="en_US"';

--selecting a number casted from a string in the specified format
SELECT TO_NUMBER('-1234');

-1234

SELECT TO_NUMBER('12345','999999');

SELECT TO_NUMBER('12,345.67','99,999.999');

12345.670

SELECT TO_NUMBER('12345.67','99999.999');

12345.670

The following example shows command execution on the database by setting the value of the intl_number_lang system parameter to “de_DE”. In the number output format of most European countries, such as Germany and France, “.” is the cipher identifier and “,” is the decimal point symbol.

SET SYSTEM PARAMETERS 'intl_number_lang="de_DE"';
SELECT TO_NUMBER('12.345,67','99.999,999');

12345.670

TO_TIME ¶

TO_TIME(string[, format[, date_lang_string_literal]])¶

The TO_TIME function interprets a character string based on the time format given as an argument, converts it to a TIME type value, and returns it. For the format, see Date/Time Format 1.

Parameters:

string – Specifies an expression that returns character string. If the value is NULL, NULL is returned.
format – Specifies a format of return value to be converted as TIME type. See Date/Time Format 1. If the value is NULL, NULL is returned.
date_lang_string_literal – Specifies the language for the input value to be applied.

Return type:

TIME

When the format argument is specified, the string is interpreted according to the specified language. At this time, the language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language specified when creating DB is applied. When the format parameter does not correspond to the given string, an error is returned.

For example, when a language is “de_DE” and string is “10:23:00 Nachm.”, and format is “HH/MI/SS/AM, it is interpreted as 10:23:00 PM.

When the format argument is omitted, string is interpreted as the CUBRID default format (refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is “de_DE”, the acceptable strings for TIME type are “HH:MI:SS AM”, CUBRID default format and “HH24:MI:SS”, “de_DE” default format.

Note

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

The following example shows execution of the database by setting the value of system parameter intl_date_lang as “en_US”.

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';

--selecting a time type value casted from a string in the specified format

SELECT TO_TIME ('13:10:30');

01:10:30 PM

SELECT TO_TIME('HOUR: 13 MINUTE: 10 SECOND: 30', '"HOUR:" HH24 "MINUTE:" MI "SECOND:" SS');

01:10:30 PM

SELECT TO_TIME ('13:10:30', 'HH24:MI:SS');

01:10:30 PM

SELECT TO_TIME ('13:10:30', 'HH12:MI:SS');

ERROR: Conversion error in date format.

The following example shows the case when the system parameter intl_date_lang is “de_DE”.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_TIME('13:10:30');

01:10:30 PM

SELECT TO_TIME('10:23:00 Nachm.', 'HH:MI:SS AM');

10:23:00 PM

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is “ko_KR” and “tr_TR” except “en_US”. If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

TO_TIMESTAMP ¶

TO_TIMESTAMP(string[, format[, date_lang_string_literal]])¶

The TO_TIMESTAMP function interprets a character string based on the time format given as an argument, converts it to a TIMESTAMP type value, and returns it. For the format, see Date/Time Format 1.

Parameters:

string – Specifies an expression that returns character string. If the value is NULL, NULL is returned.
format – Specifies a format of return value to be converted as TIMESTAMP type. See Date/Time Format 1. If the value is NULL, NULL is returned.
date_lang_string_literal – Specifies the language for the input value to be applied.

Return type:

TIMESTAMP

When the format argument is specified, the string is interpreted according to the specified language. The language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language specified when creating DB is applied.

For example, when a language is “de_DE” and string is “12/mai/2012 12:10:00 Nachm.”, and format is “DD/MON/YYYY HH:MI:SS AM”, it is interpreted as May 12th, 2012, 12:10:00 AM. When the format parameter specified does not correspond to the given string, an error is returned.

When the format argument is omitted, string is interpreted as the CUBRID default format(refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is “de_DE”, the acceptable strings for TIMESTAMP type are “HH:MI:SS AM MM/DD/YYYY”, CUBRID default format and “HH24:MI:SS DD.MM.YYYY”, “de_DE” default format.

The following example shows execution of the database by setting the value of system parameter intl_date_lang as “en_US”.

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';

--selecting a timestamp type value casted from a string in the specified format

SELECT TO_TIMESTAMP('13:10:30 12/25/2008');

01:10:30 PM 12/25/2008

SELECT TO_TIMESTAMP('08-Dec-25 13:10:30', 'YY-Mon-DD HH24:MI:SS');

01:10:30 PM 12/25/2008

SELECT TO_TIMESTAMP('YEAR: 2008 DATE: 12-25 TIME: 13:10:30', '"YEAR:" YYYY "DATE:" MM-DD "TIME:" HH24:MI:SS');

01:10:30 PM 12/25/2008

The following example shows the case when the system parameter intl_date_lang is “de_DE”.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_TIMESTAMP('13:10:30 25.12.2008');

01:10:30 PM 12/25/2008

SELECT TO_TIMESTAMP('10:23:00 Nachm.', 'HH12:MI:SS AM');

10:23:00 PM 08/01/2012

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is “ko_KR” and “tr_TR” except “en_US”. If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

TO_TIMESTAMP_TZ ¶

TO_TIMESTAMP_TZ(string[, format[, date_lang_string_literal]])¶

TO_TIMESTAMP_TZ function is the same as TO_TIMESTAMP() function except that this function can include a timezone information on this input string.

rtype:

TIMESTAMPTZ

SELECT TO_TIMESTAMP_TZ('13:10:30 Asia/Seoul 12/25/2008', 'HH24:MI:SS TZR MM/DD/YYYY');

01:10:30 PM 12/25/2008 Asia/Seoul