Casting from Date and Time Data Types to Other SQL Data Types

Go Up to Using CAST( ) to Convert Dates and Times

The following table shows the SQL data types into which the DATE, TIME, and TIMESTAMP data types can be cast:

CAST data type at right TO data type below	`TIMESTAMP`	`DATE`	`TIME`
`SMALLINT` `INTEGER` `FLOAT` `DOUBLE PRECISION` `NUMERIC` `DECIMAL`	Error	Error	Error
`VARCHAR(n)` `CHAR(n)` `CSTRING(n)`	Succeeds if n is 24 characters or more; resulting string is in format: YYYY-MM-DD HH:MM:SS.thou	Succeeds if n is 10 characters or more; resulting string is in format: YYYY-MM-DD	Succeeds if n is 10 characters or more; resulting string is in format: HH:MM:SS.thou
`BLOB`	Error	Error	Error
`TIMESTAMP`	Always succeeds	Succeeds: time portion set to 0:0:0.0000	Succeeds: date portion set to 17 November, 1858
`DATE`	Succeeds: date portion of `TIMESTAMP` is the result	Always succeeds	Error
`TIME`	Succeeds: time portion of `TIMESTAMP` is the result	Error	Always succeeds

Casting DATE to a 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.

Casting a string to a date permits strings of the form:

‘yyy-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 three-letter abbreviation in English for the two-digit numeric month. However, the order must always be four-digit year, then month, then day.

The following forms are also acceptable:

‘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 enter a date with only two digits for the year, InterBase uses its “sliding window” algorithm to assign a century to the years.

If you write out the month 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 enters the date “January 22, 2043”:

INSERT INTO t1 VALUES (‘01/22/43’);

Advance To:

Using Date Literals