Using CAST( ) to Convert Dates and Times

From InterBase

Go Up to Working with Dates and Times


You can use the built-in CAST() function in SELECT statements to translate between date and time data types and character-based data types, for example:

  • DATE, TIME or TIMESTAMP data type into a CHAR data type.

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;
  • CHAR data type into a DATE TIME or TIMESTAMP data type.
  • DATE or TIME data type into a TIMESTAMP data type.
  • TIMESTAMP data type into a DATE or TIME data type.

You cannot cast a date or time data type to or from BLOB, SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, NUMERIC, or DECIMAL data types.

Typically, CAST() is used in the WHERE clause to compare different data types. The syntax for CAST() is: 

CAST (<value> AS <data_type>)

In the following WHERE clause, CAST()is translates a CHAR data type, INTERVIEW_DATE, to a DATE data type to compare against a DATE data type, HIRE_DATE: 

… WHERE HIRE_DATE = CAST(INTERVIEW_DATE AS DATE);

In the next example, CAST() translates a DATE data type into a CHAR data type: 

… WHERE CAST(HIRE_DATE AS CHAR) = INTERVIEW_DATE;

CAST() also can be used to compare columns with different data types in the same table, or across tables.

The following two sections show the possible conversions to and from datetime (DATE, TIME, and TIMESTAMP) data types and other SQL data types.

For more information about CAST(), see Working with Data.

Topics

Advance To:

Retrieved from "https://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Using_CAST(_)_to_Convert_Dates_and_Times&oldid=26132"