BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  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. 

Element

Format

D

Day of the Week as in 1 thru 7.

DD

Day of the Month as in 1 thru 31.

DDD

Day of the Year as in 1 thru 366.

DY

Day of the Week Abbreviated  Mon thru Sun

DAY

Day of the Week  Monday, TuesdayÖ

J

Julian Date.  Number of days since 4712 BC.

W

Week of the Month as in 1 thru 5.

WW

Week of the Year as in 1 thru 53.

MM

Month (two digits) as in 01 thru 31.

MON

Month Abbreviated   Jan thru Dec.

MONTH

Month Spelled out.  January thru December.

YY

Year, last two digits as in 04 and 05.

YYYY

Year, four digit as in 2004 and 2005.

YEAR

Year spelled out.

RR

Year in 2 digits converted as per Y2K Rules.

HH

Hour in 12 hour clock.  1 thru 12.

HH24

Hour in 24 hour clock   1 thru 24.

MI

Minutes as in 1 thru 59.

SS

Seconds as in 1 thru 59.

SSSSS

Seconds of the day as in 1 thr 86399.

AM, PM

Meridian Indicator

A.M., P.M.

Meridian Indicator with periods.

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

   

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.