CAST IN ORACLE - Online Tech Support
  Home
 

This page is based on examples to be easier to follow. The Oracle Timestamp is more accurate than Oracle Date and it keeps a location stamp. They are the two main reasons why programmers are using the Oracle Timestamp. The location stamp is important because the same time in hours can mean different moments in different places on world. For example 1 pm in London means 8 am in New York, so using Oracle Date we do think about different moments. Timestamp in Oracle can solve the problem and we do have more exact time for transactions.

The first example is with Oracle CURRENT_TIMESTAMP and the difference between mostly used Oracle SYSTIMESTAMP is that the Oracle CURRENT_TIMESTAMP shows time location in words. The CURRENT_TIMESTAMP is used to show us – to people more readable way the Oracle timestamp value.

SELECT CURRENT_TIMESTAMP 
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The second Online Tech Support example is with Oracle SYSTIMESTAMP and as you the time location has changed to the GMT number +1 that is the same as Europe/London.

SELECT SYSTIMESTAMP
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To take out some value from Oracle Timestamp you can use Oracle Extract function. You can extract for example SECOND, HOUR, MINUTE, DAY, MONTH or YEAR value using the same keywords. The Oracle Extract syntax is following.

EXTRACT(<extracting_value_name> FROM <timestamp_type>)

And on the example below we have taken out a day number as this moment it was April 11th 2013.

SELECT EXTRACT(DAY FROM SYSTIMESTAMP) AS Timestamp_Day
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To turn an Oracle Timestamp a date you can use the Oracle Trunc function. The function not only will turn the Timestamp a date but also removes all stamp values. You can truncate date model names like minutes (“MI“), hours (“HH“), DAY, MONTH or YEAR.

TRUNC(<timestamp_value>,<date_model_name>)

Using Oracle Trunc without any additional setting will truncate the date from all time value. Take a look at the example below.

SELECT TRUNC(SYSTIMESTAMP) AS Truncated_Timestamp
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The second example with Oracle Trunc we have set the truncation limit to minutes (“MI“). The Oracle Trunc function will truncate from Timestamp all values that are smaller than minutes and stamps will be included into the removing list.

SELECT TRUNC(SYSTIMESTAMP,'MI') AS Truncated_Timestamp
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Another way to turn Oracle Timestamp to a date without truncating anything is to use Oracle Cast. You can cast different values to other types and the same you can to with Oracle Timestamp. The Oracle Cast syntax is:

CAST(<timestamp_value> AS <new_value_name>)

On the Oracle Cast example the Timestamp will be cast to Oracle Date. As you see on the following output all time values are remained in the date.

SELECT CAST(systimestamp AS DATE) AS THE_DATE
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To know more deep about the Oracle Timestamp attributes you can take a look at function Oracle Dump. The function returns description of timestamp where you can find in present the data type code, length in bytes, and internal representation of expr. Those attributes can be useful when you need to check if your timestamp value has been converted correctly.

DUMP(<timestamp_value>)

The example above demonstrating the output of Oracle Dump function with Oracle Timestamp.

SELECT DUMP(SYSTIMESTAMP)
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle timestamp oracle timestamp in oracle sql database oracle database sql oracle EXTRACT oracle TRUNC oracle CAST oracle SYSTIMESTAMP oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

It is important to know that using Oracle Timestamp in Oracle database table may cause problems for some applications. For example application Oracle Forms belongs to the same company as Oracle database but Oracle Forms would not compile when table has Timestamp type columns. In this case the Timestamp should be stored in VARCHAR2 and using Oracle Cast function can be returned to Oracle Timestamp type. Still if it’s possible keep the Oracle Timestamp values in their own type to avoid the extra work of converting types.



See Also:
Home Oracle Select

 

This tutorial is based on examples to be easier to follow. The Oracle Cast function allows converting one built-in type of value to another one. In other-words using Oracle Cast you can change a value to a different value type. Also Cast can be used to insert a set of data into a collection type. The Oracle Cast syntax for converting a value to another type is following:

CAST (<value> AS <convert_into_type_name>);

The first Oracle Cast example will convert a date value to a Timestamp. You are able to see how the time digits became longer in the output and since a Timestamp is more accurate than a date value, it stores smaller units than a date. Of course the conversion is missing the time units and they are all set to zero.

 SELECT CAST (TO_DATE ('11/04/2013','DD/MM/YYYY')
              AS TIMESTAMP WITH LOCAL TIME ZONE) AS my_timestamp
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

This example below is an opposite way conversion than the first example because now we will convert a Timestamp value to a date. Take a look at the SQL query output to see that time zone attributes are missing since a date don’t have them.

 SELECT CAST(systimestamp AS DATE) AS THE_DATE
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

To cast a number to a text keep in mind that a number doesn’t keep zero values in front and you can’t save them even with Oracle Cast function. This is one of the reasons why mobile numbers and ID numbers are stored as text to keep them with their original look.

 SELECT CAST(0000111001 AS VARCHAR2(30)) AS my_text
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

The fourth example shows how to convert a Rowid value into text. The Rowid values are quite useful to find out the certain row in a table. They are widely used in debugging process to point to a certain row that caused a trouble.

 SELECT CAST(rowid AS VARCHAR2(30)) AS my_text
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

Oracle Cast can be used with the Oracle collection types and the advantage is that you can insert them into a type inside SQL query and that way you code will be smaller and cleaner. The Oracle Cast syntax for casting a data set into a collection type is following:

CAST (MULTISET (<select_statement>) AS <convert_to_a_collection_type_name>);

To try the MULTISET keyword in Oracle Cast function we need a collection type. The following script will create a type named ROWNUMS_T as a table of numbers.

CREATE TYPE rownums_t AS TABLE OF NUMBER;
/

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

Now we need a Select query that returns only numbers. To keep this example as simple as possible we are going to use the Oracle Dual table and hierarchical operator CONNECT BY to create 10 lines with number from 1 to 10. Take a look at the following SQL query.

 SELECT rownum
     FROM DUAL
  CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

We are ready to try out the example since we did create the ROWNUMS_T type and we have a query that returns only number (take a look at the SQL query above). We will use both objects in the Oracle Cast function in the example above. The Oracle SQL Developer shows the context of the ROWNUMS_T type and they are numbers from 1 to 10.

 SELECT du1.dummy,
    CAST (MULTISET (SELECT rownum
                      FROM DUAL
                   CONNECT BY rownum < 11)
                     AS rownums_t) as casted_rownums
   FROM dual du1;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

Take a look at the table below to see values that can be used with Oracle Cast function.


See Also:
Home Oracle Select

Casting Built-In Data Types:

from BINARY_FLOAT, BINARY_DOUBLE from CHAR, VARCHAR2 from NUMBER from DATETIME / INTERVAL (Note 1) from RAW from ROWID, UROWID (Note 2) from NCHAR, NVARCHAR2
to BINARY_FLOAT, BINARY_DOUBLE Y Y Y Y
to CHAR, VARCHAR2 Y Y Y Y Y Y
to NUMBER X Y Y Y
to DATE, TIMESTAMP, INTERVAL Y Y
to RAW Y Y
to ROWID, UROWID Y Y
to NCHAR, NVARCHAR2 Y Y Y Y Y Y

Note 1: Datetime/interval includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.

Note 2: You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.