Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

TO_DATETIME Function

Description

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 TO_CHAR Function (date_time).

When the format argument is assigned, 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. In this case, the language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language used is the language specified in the intl_number_lang system parameter and when the value of intl_number_lang is not set, the language specified in the CUBRID_LANG environment variable is used. When the format parameter specified does not correspond to the given string, an error is returned.

When the format argument is not set, string is interpreted based on the default output format of the language set by intl_date_lang or CUBRID_LANG (see the table Default Date/Time Output Format for Each Language of the TO_CHAR Function (date_time). For example, a language is "de_DE", the default format of the DATETIME type is "HH24:MI:SS.FF DD.MM.YYYY".

Syntax

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

 

string :

character strings

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'

  • 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 DATETIME type. See the "Default Date-Time Format" table of TO_CHAR Function (date_time). If the value is NULL, NULL is returned.
  • date_lang_string_literal: Specifies the language for the input value to be applied.

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

Example

The following example shows execution of the database by setting the environment variable CUBRID_LANG 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');

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

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

 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".

SELECT TO_DATETIME('13:10:30.999 25.12.2012');

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

   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

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

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 Remark in the TO_CHAR Function.