Casting Date/time Data Types
Go Up to DATE, TIME, and TIMESTAMP Data Types
InterBase 6 and later dialect 3 no longer allow the use of the CAST operator to remove the date portion of a timestamp by casting the timestamp value to a character value. When you cast a TIMESTAMP to a CHAR or CHAR in dialect 3, the destination type must be at least 24 characters in length or InterBase will report a string overflow exception. This is required by the SQL3 standard.
You can use the CAST() function in SELECT statements to translate between date/time data types and various character-based data types. The character data type must be at least 24 characters in length. You can, however, cast a TIMESTAMP to a DATE and then cast the DATE to a CHAR of less than 24 characters. For example:
- SELECT CAST (CAST (timestamp_col AS DATE) AS CHAR(10)) FROM table1;
It is not possible to cast a date/time data type to or from BLOB, SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, NUMERIC, or DECIMAL data types.
For more information, refer to “Using CAST() to convert dates and times” in the Embedded SQL Guide.
The table below outlines the results of casting to date/time data types:
|
Cast From |
To | ||
|---|---|---|---|
TIMESTAMP
|
DATE
|
TIME
| |
|
|
String must be in format YYYY-MM-DD HH:MM:SS.thousands |
See below. |
String must be in format HH:MM:SS.thousands |
|
|
Always succeeds |
Date portion of timestamp |
Time portion of timestamp |
|
|
Always succeeds; time portion of timestamp set to 0:0:0.0000 |
Always succeeds |
Error |
|
|
Always succeeds; date portion of timestamp set to current date |
Error |
Always succeeds |
Casting DATE to string results in YYYY-MM-DD where “MM” is a two-digit month. If the result does not fit in the string variable, a string truncation exception is raised. In earlier versions, this case results in DD-Mon-YYYY HH:mm:SS.hundreds where “Mon” was a 3-letter English month abbreviation. Inability to fit in the string variable resulted in a silent truncation.
Casting a string to a date now permits strings of the form:
'yyyy-mm-dd' 'yyyy/mm/dd' 'yyyy mm dd' 'yyyy:mm:dd' 'yyyy.mm.dd'
In all of the forms above, you can substitute a month name or 3-letter abbreviation in English for the 2-digit numeric month. However, the order must always be 4-digit year, then month, then day.
In previous versions of InterBase, you could enter date strings in a number of forms, including ones that had only two digits for the year. Those forms are still available in InterBase 6 and later. If you enter a date with only two digits for the year, InterBase uses its “sliding window” algorithm to assign a century to the years.
The following forms were available in earlier versions of InterBase and are still permitted in InterBase 6 and later:
'mm-dd-yy' 'mm-dd-yyyy' 'mm/dd/yy' 'mm/dd/yyyy' 'mm dd yy' 'mm dd yyyy' 'mm:dd:yy' 'mm:dd:yyyy' 'dd.mm.yy' 'dd.mm.yyyy'
If you write out the month name in English or use a three-character English abbreviation, you can enter either the month or the day first. In the following examples, “xxx” stands for either a whole month name or a three-letter abbreviation. All of the following forms are acceptable:
'dd-xxx-yy' 'dd-xxx-yyyy' 'xxx-dd-yy' 'xxx-dd-yyyy' 'dd xxx yy' 'dd xxx yyyy' 'xxx dd yy' 'xxx dd yyyy' 'dd:xxx:yy' 'dd:xxx:yyyy' 'xxx:dd:yy' 'xxx:dd:yyyy'
For example, the following INSERT statements all insert the date “January 22, 1943”:
INSERT INTO t1 VALUES ('1943-01-22');
INSERT INTO t1 VALUES ('01/22/1943');
INSERT INTO t1 VALUES ('22.01.1943');
INSERT INTO t1 VALUES ('jan 22 1943');
The following statement would enter the date “January 22, 2043”: INSERT INTO t1 VALUES ('01/22/43');
The table below outlines the results of casting from date/time data types:
| Cast From | To CHAR(<n>), CHARACTER (<n>), or CSTRING(<n>)
|
|---|---|
|
|
Succeeds if <n> is 24 or more. Resulting string is in format YYYY-MM-DD HH:MM:SS.thousands. |
|
|
Succeeds if <n> is 10 or more. Resulting string is in the format YYYY-MM-DD. |
|
|
Succeeds if <n> is 13 or more. Resulting string is the format HH:MM:SS.thousands. |