Mar 312015
 

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;

TS_TO_DAT

———

31-MAR-15

To convert a DATE to a TIMESTAMP do the following.

SELECT CAST(SYSDATE AS TIMESTAMP) date_to_ts FROM   dual;

DATE_TO_TS

—————————————————————————

31-MAR-15 09.21.45.000000 AM

CAST Function

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.

Syntax

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
 SQL data types Conversion List

Explicit conversions between source types and target types for SQL data types

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)