TO_TIMESTAMP Function

Description

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 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 AM. 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 according to default format 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".

TO_TIMESTAMP(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 TIMESTAMP 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.

Example

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

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

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

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

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

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

SELECT TO_TIMESTAMP('13:10:30 25.12.2008');

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

   to_timestamp('10:23:00 Nachm.', 'HH12:MI:SS AM')

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

  10:23:00 PM 08/01/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_TIMESTAMP 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.