STR_TO_DATE Function

Description

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 and it is one of the DATETIME, DATE, and TIME types.

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

When the format argument is assigned, the string is interpreted according to the specified language. At that time, the language specified to the intl_date_lang system parameter 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 intl_date_lang value is not set, the language applied to the CUBRID_LANG environment variable is applied. When the format argument specified is not corresponding 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 (see "API Reference > JDBC API > JDBC Programming > Connection Configuration").

Syntax

STR_TO_DATE(string, format)

• string: All character string types can be specified.
• format: Specifies the format to interpret the character string. You should use character strings including % for the format specifiers. See the table, date/time format 2 of DATE_FORMAT Function.

Example

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

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

 str_to_date('01,5,2013', '%d,%m,%Y')

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

  05/01/2013

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

 str_to_date('May 1, 2013', '%M %d,%Y')

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

  05/01/2013

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

 str_to_date('13:30:17', '%h:%i')

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

  01:30:00 PM

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

 str_to_date('09:30:17 PM', '%r')

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

  09:30:17 PM

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

 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.

SELECT STR_TO_DATE('3 Oktober 2009', '%d %M %Y');

   str_to_date('3 Oktober 2009', '%d %M %Y')

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

  10/03/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).

When the charset is ISO-8859-1, the language can be changed to "ko_KR" or "tr_TR" only by using intl_date_lang or CUBRID_LANG (environment variable) except "en_US". If the charset is UTF-8, the language can be changed to any language supported by CUBRID. For a more detailed description, see TO_CHAR Function (date_time)  > Remark.