Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 9.0.0 | 

Casting a String to Date/Time Type

Recommended Format for Strings in Date/Time Type

When you casting a string to Date/Time type by using the CAST function, it is recommended to write the string in the following format: Note that date/time string formats used in the CAST function are not affected by locale (which is specified as the CUBRID_LANG environment variable).

  • DATE Type
  • YYYY-MM-DD
  • MM/DD/YYYY
  • TIME Type
  • HH:MM:SS ["AM"|"PM"]
  • DATETIME Type
  • YYYY-MM-DD HH:MM:SS[.msec] ["AM"|"PM"]
  • TIMESTAMP Type
  • YYYY-MM-DD HH:MM:SS ["AM"|"PM"]
Available DATE String Format

[year sep] month sep day

  • 2011-04-20: April 20th, 2011
  • 04-20: April 20th of this year

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

month/day[/year]

  • 04/20/2011: April 20th, 2011
  • 04/20: April 20th of this year

If you do not use a separator (sep), strings are recognized in the following format. It is allowed to use 1, 2, and 4 digits for years and 1 and 2 digits for months. For day, you should always enter 2 digits.

YYYYMMDD

YYMMDD

YMMDD

MMDD

MDD

  • 20110420: April 20th, 2011
  • 110420: April 20th, 2011
  • 420: April 20th of this year
Available TIME String Format

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

  • 09:10:15.359 am: 9 hours 10 minutes 15 seconds AM (0.359 seconds will be truncated)
  • 09:10:15: 9 hours 10 minutes 15 seconds AM
  • 09:10: 9 hours 10 minutes AM
  • :10: 12 hours 10 minutes AM

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

  • 20110420091015.359 am: 9 hours 10 minutes 15 seconds AM
  • 0420091015: 9 hours 10 minutes 15 seconds AM

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

  • 091015.359 am: 9 hours 10 minutes 15 seconds AM
  • 91015: 9 hours 10 minutes 15 seconds AM

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

  • 1015.359 am: 12 hours 10 minutes 15 seconds AM
  • 1015: 12 hours 10 minutes 15 seconds AM

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

  • 15.359 am: 12 hours 15 seconds AM
  • 15: 12 hours 15 seconds AM

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 DATETIME String Format

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

  • 04-20 09: April 20th of this year, 9 hours AM

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

  • 04/20 09: April 20th of this year, 9 hours AM

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

  • 2011-04-20 09: April 20th, 2011, 9 hours AM

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

  • 04/20/2011 09: April 20th, 2011, 9 hours AM

YYMMDDH (It is allowed only when time format is one digit.)

  • 1104209: April 20th, 2011, 9 hours AM

YYMMDDHHMM[SS[.msec]]

  • 1104200910.359: April 20th, 2011, 9 hours 10 minutes AM (0.359 seconds will be truncated)
  • 110420091000.359: April 20th, 2011, 9 hours 10 minutes 0.359 seconds AM

YYYYMMDDHHMMSS[.msec]

  • 201104200910.359: November 4th, 2020 8 hours 9 minutes 10.359 seconds PM
  • 20110420091000.359: April 20th, 2011, 9 hours 10 minutes 0.359 seconds AM
Available Time-Date String Format

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

  • 09:10:15.359 am 2011-04-20: April 20th, 2011, 9 hours 10 minutes 15.359 seconds AM
  • :10 04-20: April 20th of this year, 12 hours 10 minutes AM

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

  • 09:10:15.359 am 04/20/2011: April 20th, 2011, 9 hours 10 minutes 15.359 seconds AM
  • :10 04/20: April 20th of this year, 12 hours 10 minutes AM

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

  • 09:10:15.359 am 04-20: April 20th of this year, 9 hours 10 minutes 15.359 seconds AM
  • 09 04-20: April 20th of this year, 9 hours AM

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

  • 09:10:15.359 am 04/20: April 20th of this year, 9 hours 10 minutes, 15.359 seconds AM
  • 09 04/20: April 20th of this year, 9 hours AM
Rules

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

The rules for the separator string are as follows:

  • You should always use one colon (:) as a separator for the TIME separator.
  • DATE and DATETIME strings can be represented as a series of numbers without the separator sep), and non-alphanumeric characters can be used as separators. The DATETIME string can be divided into Time and Date with a space.
  • Separators should be identical in the input string.
  • For the Time-Date string, you can only use colon (:) for a Time separator and hyphen (-) or slash (/) for a Date separator. If you use a hyphen when entering date, you should enter like yyyy-mm-dd; in case of  a slash, enter like mm/dd/yyyy.

The following rules will be applied in the part of date.

  • You can omit the year as long as the syntax allows it.
  • If you enter the year as two digits, it represents the range from 1970-2069. That is, if YY<70, it is treated as 2000+YY; if YY>=70, it is treated as 1900+YY. If you enter one, three or four digit numbers for the year, the numbers will be represented as they are.
  • A space before and after a string and the string next to the space are ignored. The am/pm identifier for the DATETIME and TIME strings can be recognized as part of TIME value, but are not recognized as the am/pm identifier if non-space characters are added to it.

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.

  • If you convert the DATE string to the DATETIME type, the time value will be '00:00:00.'
  • If you convert the TIME string to the DATETIME type, colon (:) is recognized as a date separator, so that the TIME string can be recognized as a date string and the time value will be '00:00:00.'
  • If you convert the DATETIME string to the DATE type, the time part will be ignored from the result but the time input value format should be valid.
  • You can covert the DATETIME string to the TIME type, and you must follow the following rules.
    • The date and time in the string must be divided by at least one blank.
    • The date part of the result value is ignored but the date input value format should be valid.
    • The year in the date part must be over 4 digits (available to start with 0) or the time part must include hours and minutes ([H]H:[M]M) at least. Otherwise the date pate are recognized as the TIME type of the [MM]SS format, and the following string will be ignored.
  • If the one of the units (year, month, date, hour, minute and second) of the DATETIME string is greater than 999999, it is not recognized as a number, so the string including the corresponding unit will be ignored. For example, in '2009-10-21 20:9943:10', an error occurs because the value in minutes is out of the range. However, if '2009-10-21 20:1000123:10' is entered,'2009' is recognized as the the TIME type of the MMSS format, so that TIME '00:20:09' will be returned.
  • If you convert the time-date sting to the TIME type, the date part of the string is ignored but the date part format must be valid.
  • All input strings including the time part allow [.msec] on conversion, but only the DATETIME type can be maintained. If you convert this to a type such as DATE, TIMESTAMP or TIME, the msec value is discarded.
  • All conversions in the DATETIME, TIME string allow English locale following after time value or am/pm specifier written in the current locale of a server.
Example

SELECT CAST('420' AS DATE);

 

   cast('420' as date)

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

  04/20/2012

 

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