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

From InterBase
Jump to: navigation, search

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: