The CAST function can be used to convert a TIMESTAMP to a DATE and vice versa. First let’s convert a TIMESTAMP to a DATE.
SELECT CAST(SYSTIMESTAMP AS DATE) ts_to_date FROM dual;
To convert a DATE to a TIMESTAMP do the following.
SELECT CAST(SYSDATE AS TIMESTAMP) date_to_ts FROM dual;
31-MAR-15 09.21.45.000000 AM
The CAST function converts a value from one data type to another and provides a data type to a dynamic parameter (?) or a NULL value. CAST expressions are permitted anywhere expressions are permitted.
CAST ( [ Expression | NULL | ? ] AS Datatype)
The data type to which you are casting an expression is the target type. The data type of the expression from which you are casting is the source type.
CAST conversions among SQL-92 data types
The following table shows valid explicit conversions between source types and target types for SQL data types. This table shows which explicit conversions between data types are valid. The first column on the table lists the source data types. The first row lists the target data types. A “Y” indicates that a conversion from the source to the target is valid. For example, the first cell in the second row lists the source data type SMALLINT. The remaining cells on the second row indicate the whether or not you can convert SMALLINT to the target data types that are listed in the first row of the table.
|Table 1. Explicit conversions between source types and target types for SQL data types|