Oracle Faq 11-20
How To Write Text Literals?
There are
several ways to write text literals as shown in the following samples:
SELECT
'FYICenter.com' FROM DUAL -- The most common format
FYICenter.com
SELECT
'It''s Sunday!' FROM DUAL -- Single quote escaped
It's
Sunday!
SELECT
N'Allo, C''est moi.' FROM DUAL -- National chars
Allo,
C'est moi.
SELECT
Q'/It's Sunday!/' FROM DUAL -- Your own delimiter
It's
Sunday!
How To Write Numeric Literals?
Numeric
literals can coded as shown in the following samples:
SELECT
255 FROM DUAL -- An integer
255
SELECT
-6.34 FROM DUAL -- A regular number
-6.34
SELECT
2.14F FROM DUAL -- A single-precision floating point
2.14
SELECT
-0.5D FROM DUAL -- A double-precision floating point
-0.5
How To Write Date and Time Literals?
Date and
time literals can coded as shown in the following samples:
SELECT
DATE '2002-10-03' FROM DUAL -- ANSI date format
03-OCT-02
SELECT
TIMESTAMP '1997-01-31 09:26:50.124' FROM DUAL
31-JAN-97
09.26.50.124000000 AM
--
This is ANSI format
How To Write Date and Time Interval
Literals?
Date and
time interval literals can coded as shown in the following samples:
SELECT
DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH
FROM DUAL
-- 123 years and 2 months is added to
2002-10-03
03-DEC-25
SELECT
DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL
-- 123 years is added to 2002-10-03
03-OCT-25
SELECT
DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL
-- 299 months years is added to 2002-10-03
03-SEP-27
SELECT
TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
FROM DUAL
04-FEB-97
02.39.00.346000000 PM
SELECT
TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL
04-FEB-97
02.38.50.124000000 PM
SELECT
TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL
07-MAR-98
02.26.50.124000000 PM
SELECT
TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '400' DAY(3) FROM DUAL
07-MAR-98
09.26.50.124000000 AM
SELECT
TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '11:12:10.2222222' HOUR TO
SECOND(7) FROM DUAL
31-JAN-97
08.39.00.346222200 PM
SELECT
TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '30.12345' SECOND(2,4) FROM DUAL
31-JAN-97
09.27.20.247500000 AM
How To Convert Numbers to
Characters?
You can
convert numeric values to characters by using the TO_CHAR() function as shown
in the following examples:SELECT TO_CHAR(4123.4570) FROM DUAL
123.457
SELECT TO_CHAR(4123.457, '$9,999,999.99') FROM DUAL
$4,123.46
SELECT TO_CHAR(-4123.457, '9999999.99EEEE') FROM DUAL
-4.12E+03
How To Convert Characters to
Numbers?
You can
convert characters to numbers by using the TO_NUMBER() function as shown in the
following examples:SELECT TO_NUMBER('4123.4570') FROM DUAL
4123.457
SELECT TO_NUMBER(' $4,123.46','$9,999,999.99') FROM DUAL
4123.46
SELECT TO_NUMBER(' -4.12E+03') FROM DUAL
-4120
How To Convert Dates to Characters?
You can
convert dates to characters using the TO_CHAR() function as shown in the following
examples:SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
-- SYSDATE returns the current date
07-MAY-2006
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;
2006/05/07
SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;
MAY 07, 2006
SELECT TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY') FROM DUAL;
May 7, 2006
SELECT TO_CHAR(SYSDATE, 'fmDAY, MONTH DD, YYYY') FROM DUAL;
SUNDAY, MAY 7, 2006
How To Convert Characters to Dates?
You can
convert dates to characters using the TO_DATE() function as shown in the
following examples:SELECT TO_DATE('07-MAY-2006', 'DD-MON-YYYY') FROM DUAL;
07-MAY-06
SELECT TO_DATE('2006/05/07 ', 'YYYY/MM/DD') FROM DUAL;
07-MAY-06
SELECT TO_DATE('MAY 07, 2006', 'MONTH DD, YYYY')
FROM DUAL;
07-MAY-06
SELECT TO_DATE('May 7, 2006', 'fmMONTH DD, YYYY') FROM DUAL;
07-MAY-06
SELECT TO_DATE('SUNDAY, MAY 7, 2006',
'fmDAY, MONTH DD, YYYY') FROM DUAL;
07-MAY-06
How To Convert Times to Characters?
You can
convert dates to characters using the TO_CHAR() function as shown in the
following examples:SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
04:49:49
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS.FF') FROM DUAL;
-- Error: SYSDATE has no fractional seconds
SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL;
16:52:57.847000000
SELECT TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL;
-- Seconds past midnight
69520
>>page3
No comments