Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Advanced Oracle SQL: Conversion Functions

Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Implicit conversion exists between numeric, date-time and character datatypes.

SELECT
   LENGTH(999),
   SINH('000'),
   LAST_DAY('01-FEB-2008')
FROM
   DUAL;
LENGTH(999) SINH('000') LAST_DAY(
----------- ----------- ---------
          3           0 29-FEB-08

The length function converts number 999 to a string, hyperbolic sine converts string '000' to a number and last day of month converts string '01-FEB-2008' to a date, providing the session NLS setting is appropriate (DD-MON-YYYY). Explicit conversion is preferred.

A number can be converted to a character and inversely:

SELECT
   CHR(65),
   ASCII('Z')
FROM
   DUAL;
C ASCII('Z')
- ----------
A         90

The CHR converts a number to an ASCII character and the inverse function ASCII converts a character to its corresponding ASCII value.

SELECT
   DUMP
   (
      HEXTORAW
      (
         '4F5241434C45'
      ),
      17
   ) DUMP,
   VSIZE
   (
      HEXTORAW
      (
         '4F5241434C45'
      )
   ) VSIZE
FROM
   DUAL;
DUMP                      VSIZE
------------------------- -----
Typ=23 Len=6: O,R,A,C,L,E     6

HEXTORAW converts a string that represents a hexadecimal number to raw data. HEXTORAW is useful when comparing a raw value from a table or a function to a literal. DUMP provides:

octal (8), decimal (10), hexadecimal (16) or character (17) dump.

The type 23 represents the RAW datatype. The length is 6 bytes. The function VSIZE also returns the length in bytes.

SELECT
   TO_NUMBER
   (
      RAWTOHEX
      (
         SYS_GUID()
      ),
      'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
   )
FROM
   DUAL;
TO_NUMBER(RAWTOHEX(SYS_GUID()),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
------------------------------------------------------------------
                            94677074495605736132992199838641691196

The inverse function RAWTOHEX converts raw data to a string containing hexadecimals. SYS_GUID generates 32 bytes of raw data. The globally unique identifier is transformed to a hexadecimal string by RAWTOHEX and then to an integer by TO_NUMBER.

SELECT
   SYSDATE,
   EXTRACT(YEAR FROM SYSDATE) YYYY,
   EXTRACT(MONTH FROM SYSDATE) MM,
   EXTRACT(DAY FROM SYSDATE) DD
FROM
   DUAL;

SYSDATE     YYYY  MM  DD
---------- ----- --- ---
2008-02-08  2015   2   8

EXTRACT with a date returns numeric values for the year, the month or the day. It is not possible to extract the time out of the date datatype with this function.

SELECT
   CURRENT_TIMESTAMP,
   EXTRACT(HOUR FROM CURRENT_TIMESTAMP) HH,
   EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) MI,
   EXTRACT(SECOND FROM CURRENT_TIMESTAMP) SS,
   EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) TZH,
   EXTRACT(TIMEZONE_MINUTE FROM CURRENT_TIMESTAMP) TZM,
   EXTRACT(TIMEZONE_REGION FROM CURRENT_TIMESTAMP) TZR,
   EXTRACT(TIMEZONE_ABBR FROM CURRENT_TIMESTAMP) TZA
FROM
   DUAL;

CURRENT_TIMESTAMP                  HH  MI         SS TZH TZM TZR           TZA
--------------------------------- --- --- ---------- --- --- ------------- ---
2008-02-08 13:25:13 EUROPE/ZURICH  12  25  13.103363   1   0 Europe/Zurich CET

EXTRACT with a timestamp returns numeric values for the year, the month, the day and also the hour, the minute and the second. The second may contain fractions of a second. The hour and minute are extracted at GMT+00:00 and the offset, if applicable, is retrieved from the time zone. The region and abbreviation are returned as strings.

WITH T AS
(
   SELECT
      (CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 +00:00')
         YEAR(2) TO MONTH Y2M
   FROM
      DUAL
)
SELECT
   Y2M,
   EXTRACT(YEAR FROM Y2M) YYYY,
   EXTRACT(MONTH FROM Y2M) MM
FROM
   T;

Y2M     YYYY  MM
------ ----- ---
+38-01    38   1

EXTRACT with a year-to-month interval returns numeric values for the year or the month:

WITH T AS
(
   SELECT
      (CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 +00:00')
         DAY(6) TO SECOND(6) D2S
   FROM
      DUAL
)
SELECT
   D2S,
   86400*EXTRACT(DAY FROM D2S) +
   3600*EXTRACT(HOUR FROM D2S) +
   60*EXTRACT(MINUTE FROM D2S) +
   EXTRACT(SECOND FROM D2S)
      UNIXTIME
FROM
   T;

D2S                               UNIXTIME
----------------------- ------------------
+013917 12:25:13.247043  1202473513.247043

EXTRACT with a day-to-second interval returns numeric values for the day, hour, minute and second. The interval between January 1st, 1970 and the current time is used to calculate a number of seconds that represents the UNIX time.

SELECT
   (
      TIMESTAMP '1970-01-01 00:00:00 +00:00'+
      NUMTODSINTERVAL(1202473513.247043,'SECOND')
   ) AT TIME ZONE SESSIONTIMEZONE TIMESTAMP
FROM
   DUAL;
TIMESTAMP
----------------------------------------
2008-02-08 13:25:13.247043 Europe/Zurich

The function NUMTODSINTERVAL converts a number of DAY, HOUR, MINUTE or SECOND to a day-to-second interval. Adding a number of seconds to January 1st, 1970 is useful for converting UNIX timestamps to Oracle timestamps.

The function NUMTOYMINTERVAL returns an interval from a number of YEAR or MONTH:

SELECT
   TO_DSINTERVAL('+1 2:3:4.5') D2S_SQL,
   TO_DSINTERVAL('P1DT2H3M4.5S') D2S_ISO,
   TO_YMINTERVAL('1-2') Y2M_SQL,
   TO_YMINTERVAL('P1Y2M') Y2M_ISO
FROM
   DUAL;

D2S_SQL         D2S_ISO         Y2M_SQL Y2M_ISO
--------------- --------------- ------- -------
+01 02:03:04.50 +01 02:03:04.50 +01-02  +01-02

TO_DSINTERVAL and TO_YMINTERVAL create an interval from a string. The string format cannot be changed. The two possible notations are listed. A large number of possible formats for dates and numbers exist.

SELECT
   TO_CHAR
   (
      CURRENT_TIMESTAMP,
      'FMDay ddth Month, YYYY B.C., FMHH:MI:SSXFF AM TZH:TZM'
   ) TODAY
FROM
   DUAL;

TODAY
--------------------------------------------------------------
Friday 1st February, 2015 A.D., 05:01:02.560225 PM +01:00

TO_CHAR converts a date or a timestamp to a string. Note the FM formatter: by default, words like Day or Month are padded. In English, the longest month is September, so all months will be right-padded with spaces to nine characters. The numeric values are left-padded with zero. FM provides a nicer output by removing extra spaces and leading zeroes. For hours, however, 05:01:02 looks better than 5:1:2. So the second FM formatting switches back to the default padding behavior. Note the capitalization of the format elements: Day returns Friday, DAY returns FRIDAY and day returns friday.

Also note the difference between HH and HH24. HH is often used together with AM to display the time in 12 hours format (from 1 to 12), and HH24 is the 24 hours format (from 0 to 23).

A fancy option to observe is the SP suffix, which spells the element. Unfortunately, it is not possible to choose a language other than English. The TH suffix is used for ordinal numbers.

SELECT
   TO_CHAR
   (
      TO_TIMESTAMP
      (
         TO_CHAR
         (
            42e-9
         ),
         '.FF9'
       ),
       'ff9sp ff9spth'
   ) "42"
FROM
   DUAL;
42
----------------------
forty-two forty-second

The number is expressed in nanoseconds (10-9 seconds) and spelled as a cardinal and an ordinal number.

The inverse functions TO_DATE, TO_TIMESTAMP and TO_TIMESTAMP_TZ convert a string to a date, a timestamp and a timestamp with time zone:

SELECT
   TO_DATE('1-VIII-1291 AD','DD-RM-YYYY BC') "DATE",
   TO_TIMESTAMP('3000000 20000 100000000','J SSSSS FF') "TIMESTAMP",
   TO_TIMESTAMP_TZ('4 02','DD HH24') "TIMESTAMP_TZ"
FROM
   DUAL;

DATE       TIMESTAMP                      TIMESTAMP_TZ
---------- ------------------------------ ------------------------------
1291-08-01 3501-08-15 05:33:20.100000000  2015-02-04 02:00 EUROPE/ZURICH

RM is a Roman month from I to XII, J is the Julian day, where day 1 is January 1st, 4712 BC, day 60 is March 1st, 4712 BC. Day 1 is the first supported day of the Oracle Calendar and day 5373484 is the last supported day of the Oracle Calendar, December 31st, 9999 AD. SSSSS is the number of seconds since midnight (0-86399). FF is a fraction of seconds. It is not mandatory to specify all fields. The third expression returns the 4th of the current month, at 02:00:00.00 at session time zone.

SELECT
   TO_DATE('2 FEB. 08','DD-MONTH-RRRR') DD_MONTH_RR,
   TO_DATE('2-JAN-1994','DD/MM/YY') DD_MM_YY,
   TO_DATE('06-06-2007', 'FXDD-MM-YYYY') FXDD_MM_YYYY,
   TO_DATE('1/1/08','FXFMDD/MM/RR') FXFMDD_MM_RR
FROM
   DUAL;

DD_MONTH_RR DD_MM_YY   FXDD_MM_YY FXFMDD_MM_RR
----------- ---------- ---------- ------------
2008-02-02  1994-01-02 2015-06-06 2015-01-01

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.