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:
- DATE Type
- TIME Type
- HH[:MM[:SS]] ["am"|"pm"]
- DATETIME Type
- YYYY-MM-DD HH:MM:SS[.msec] YY-MM-DD HH:MM:[SS[.msec]]
- YY-MM-DD H
- TIMESTAMP Type
- YYYY-MM-DD HH:MM:SS
- YY-MM-DD HH:MM:[SS]
- YY-MM-DD H
Available DATE String Format
[year sep] month sep day
If a separator (sep) is a slash (/), strings are recognized in the following order:
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.
Available TIME String Format
[hour]:min[:[sec]] [.[msec]] [am|pm]
- 09:10:15.359 am
- 20110420091015.359 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 String Format in Time-Date
[hour]:min[:sec[.msec]] [am|pm] sep [year-]month-day
- 09:10:15.359 am 2011-04-20
- :10 04-20
[hour]:min[:sec[.msec]] [am|pm] sep month/day[/[year]]
- 09:10:15.359 am 04/20/2011
- :10 04/20
hour[:min[:sec[.[msec]]]] [am|pm] sep [year-]month-day
- 09:10:15.359 am 04-20
- 09 04-20
hour[:min[:sec[.[msec]]]] [am|pm] sep month/day[/[year]]
- 09:10:15.359 am 04/20
- 09 04/20
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 (??? ? ?? ?? ??? ??)
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:
- 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 do not need to 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.
- For the DATE string, you can use colon (:) or other separators.
The following rules will be applied to the Date part in the string.
- 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 identifier written in the current locale of a server.
SELECT CAST('420' AS DATE);
cast('420' as date)
SELECT CAST('91015' AS TIME);
cast('91015' as time)
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