 |
|
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 2008 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, 2008 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 2008-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 2007-06-06 2008-01-01
