Converting a String to Date/Time Type

Recommended Format for Strings in Date/Time Type

When you convert a string to Date/Time type, it is recommended to write the string in the following format:

Available DATE String Format

[year sep] month sep day

If a separator (sep) is a slash (/), strings are recognized in the following order:

month/day[/year]

If you do not use a separator (sep), strings are recognized in the following format. It is allowed to use up to 4 digits for years and up to 2 digits for months. You must enter a 2-digit day.

[[[[Y]Y]YY]M]MDD

Available TIME String Format

[hour]:min[:[sec]] [.[msec]] [am|pm]

[[[[[[Y]Y]Y]Y]M]MDD]HHMMSS[.[msec]] [am|pm]

[H]HMMSS[.[msec]] [am|pm]

[M]MSS[.[msec]] [am|pm]

[S]S[.[msec]] [am|pm]

Note: The [H]H format was allowed in CUBRID 2008 R3.1 and the earlier versions. That is, the string '10' was converted to TIME '10:00:00' in the R3.1 and the earlier versions, and will be converted to TIME '00:00:10' in version R4.0 and later.

Available String Format in Time-Date

[hour]:min[:sec[.msec]] [am|pm] sep [year-]month-day

[hour]:min[:sec[.msec]] [am|pm] sep month/day[/[year]]

hour[:min[:sec[.[msec]]]] [am|pm] sep [year-]month-day

hour[:min[:sec[.[msec]]]] [am|pm] sep month/day[/[year]]

Available DATETIME String Format

[year sep] month sep day [sep] [sep] hour [sep min[sep sec[.[msec]]]]

month/day[/year] [sep] hour [sep min [sep sec[.[msec]]]]

year sep month sep day sep hour [sep min[sep sec[.[msec]]]]

month/day/year sep hour [sep min[sep sec [.[msec]]]]

YYMMDDH (??? ? ?? ?? ??? ??)

YYMMDDHHMM[SS[.msec]]

YYYYMMDDHHMMSS[.msec]

Rules

msec is a series of numbers representing milliseconds. The numbers after the fourth digit will be ignored.

sep represents the separator string allowed. The rules for the separator string are as follows:

The following rules will be applied to the Date part in the string.

The TIMESTAMP type of CUBRID consists of DATE type and TIME type, and DATETIME type consists of DATE type and TIME type with milliseconds being added to them. Input strings can include Date (DATE string), Time (TIME string), or both (DATETIME strings). You can convert a string including a specific type of data to another type, and the following rules will be applied for the conversion.

Example

SELECT CAST('420' AS DATE);

 

   cast('420' as date)

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

  04/20/2011

 

SELECT CAST('91015' AS TIME);

 

   cast('91015' as time)

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

  09:10:15 AM

 

 

SELECT CAST('110420091035.359' AS DATETIME);

 

   cast('110420091035.359' as datetime)

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

  09:10:35.359 AM 04/20/2011

 

SELECT CAST('110420091035.359' AS TIMESTAMP);

 

   cast('110420091035.359' as timestamp)

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

  09:10:35 AM 04/20/2011