Oracle Tips by Burleson

Conversion Functions

Conversion functions change data from on data type to another.  Again, there are a significant number of conversion functions; however, most are used primarily in PL/SQL  programming.  We will focus our attention on the functions most used in SQL queries.

Oracle will do a lot of conversion internally.  During assignment, the database will change a varchar2 or char type into a number or date type.  Likewise, the database will convert a number or date into a varchar2. During evaluation, the database will only convert varchar2 and char to number and date. 

What we want to cover in this section is explicitly converting between numbers, characters and dates.  You will see that there are a large number of formatting options.  This ability to format the conversion not only allows you great flexibility, but also is key in allowing you to import data from other sources that may be saved in different formats.

In Figure 2.1, we can see the three main conversion function.  to_date  converts characters to dates while to_number converts characters to numbers.  The to_char function takes either a date or a number and converts it to characters.  Let’s start with the to_char function converting dates.

to_char (date, format)

The to_char  functions will change a date to characters in the format defined in the format field.  If you do not define a format, the date will be returned in the default format set for the database.

SELECT
  TO_CHAR(SYSDATE)
FROM
  dual;
TO_CHAR(S
---------
25-JAN-05 

Formatting commands are enclosed in single quotes and are case sensitive and can include any valid date format element. 

Table 2.1: Date elements and associated formats

Let’s see some examples to clarify all these formatting command.

SELECT
  TO_CHAR(SYSDATE) ,
  TO_CHAR(SYSDATE,'MON-DD-YYYY'),
  TO_CHAR(SYSDATE,'MON:DAY:YYYY'),
  TO_CHAR(SYSDATE,'MONTH, DAY, YEAR')
FROM
  dual;

TO_CHAR(S TO_CHAR(SYS TO_CHAR(SYSDATE,'M TO_CHAR(SYSDATE,'MONTH,DAY,YEAR')
--------- ----------- ------------------ ---------------------------------
25-JAN-05 JAN-25-2005 JAN:TUESDAY  :2005 JANUARY, TUESDAY  , TWO THOUSAND FIVE

First, I retrieved the database default.  I then changed the order and format.  Notice that I used literal text between the formatting commands.  I used a dash (-) on one example and a colon (:) on the other.  Lastly, I wrote the entire date out long hand.   Next, let’s look at the time component of the date. 

SELECT
  TO_CHAR(SYSDATE) ,
  TO_CHAR(SYSDATE,'HH24'),
  TO_CHAR(SYSDATE,'HH AM'),
  TO_CHAR(SYSDATE,'HH:MM:SS AM')
FROM
  dual;

TO_CHAR(S TO TO_CH TO_CHAR(SYS
--------- -- ----- -----------
25-JAN-05 10 10 AM 10:01:06 AM

Again, I first retrieved the database default, which shows no time element at all.  I then selected the hour in 24-hour format, the hour in 12-hour format, and lastly the hours, minutes and seconds. 

Let’s put this to work.  In the PUBS schema, there is a table called sales that contains order number and order dates.  Let’s retrieve the order numbers and the order dates in a format that shows the data and hour the order was placed.

First, describe the sales table.

SQL> desc sales
 Name                Null?    Type
 ------------------- -------- ----------------------- STORE_KEY                    VARCHAR2(4)
 BOOK_KEY                     VARCHAR2(6)
 ORDER_NUMBER                 VARCHAR2(20)
 ORDER_DATE                   DATE
 QUANTITY                     NUMBER(5) 

Next retrieve the order_number and the order_date. 

SELECT
  order_number,
  order_date
FROM
  sales;
ORDER_NUMBER         ORDER_DAT
-------------------- ---------
O101                 02-JAN-02
O102                 02-JAN-02
O103                 02-JAN-02
O104                 03-JAN-02
O105                 03-JAN-02
O106                 03-JAN-02
O107                 04-JAN-02
O108                 04-JAN-02
-----------
O198                 19-MAY-02
O199                 20-MAY-02
O200                 21-MAY-02

100 rows selected.

Finally, add the formatting with the to_char function.

SELECT
  order_number "Order",
  TO_CHAR(order_date,'DD-MON-YYYY HH24') "Date"
FROM
  sales;

Order                Date
-------------------- --------------
O101                 02-JAN-2004 14
O102                 02-JAN-2004 13
O103                 02-JAN-2004 16
O104                 03-JAN-2004 08
O105                 03-JAN-2004 13
O106                 03-JAN-2004 14
O107                 04-JAN-2004 14
O108                 04-JAN-2004 09
O109                 04-JAN-2004 14
O110                 04-JAN-2004 10
O111                 04-JAN-2004 14
O112                 05-JAN-2004 15
O113                 05-JAN-2004 14
O114                 05-JAN-2004 16
O115                 07-JAN-2004 17
O116                 10-FEB-2004 13
O117                 10-FEB-2004 12
O118                 10-FEB-2004 08
O119                 10-FEB-2004 14
O120                 10-FEB-2004 08
O121                 10-FEB-2004 12
O122                 11-FEB-2004 09

Notice that I placed the order and date aliases into double quotes.  I did that because both are Oracle key words and if I did not quote them, they would confuse the SQL parser. 

Now let’s put this to use in the comparison operator.  Find all the order numbers that were placed at 2 PM (14).

 SELECT
  order_number "Order"
FROM
  sales
WHERE
  TO_CHAR(order_date,'HH24') = '14';

Order
--------------------
O101
O106
O107
O109
O111
O113
O119
O124
O129
O134
O141
O147
O151
O157
O160
O166
O172
O180
O186
O190

20 rows selected.

If I did not place single quotes around the 14 in the where clause, the query would still work.  That is because the database would implicitly convert the character hour to a number to compare with the number 14.  You would have two conversions, one implicitly (the character to number) and on explicitly (the date to character).

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm