DATE FUNCTIONS:
----------------------------
ADD_MONTHS:
this will add specified months to the given date.
---------------------
ADD_MONTHS(date,no.of
months)
SELECT
ADD_MONTHS(SYSDATE,5),ADD_MONTHS(SYSDATE,-5),ADD_MONTHS(SYSDATE,0) FROM DUAL;
SELECT
ADD_MOTHS('04/JUN/1989',3) FROM DUAL;
SELECT
ADD_MONTHS(TO_DATE('04/23/2013','DD/MM/YYYY')+5) FROM DUAL;
SELECT
ADD_MONTHS(SYSDATE,NULL) FROM DUAL; --if no.of months null it will display
nothing.
MONTHS_BETWEEN:
this will give difference months between two DATES.
----------------------------
MONTHS_BETWEEN(date1,date2):
SELECT
MONTHS_BETWEEN(SYSDATE,'12-JAN-2014') FROM DUAL;
SELECT
MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
SELECT
MONTHS_BETWEEN(ADD_MONTHS(SYSDATE+5),SYSDATE) FROM DUAL;
SELECT
MONTHS_BETWEEN(SYSDATE,TO_DATE('12-JAN-2014','DD-MON-YYYY')) FROM DUAL;
NEXT_DAY:
It produce next day of the given day from specified date.
-----------------
SELECT
NEXT_DAY(SYSDATE,'WED') FROM DUAL;
SELECT
NEXT_DAY(SYSDATE,5) FROM DUAL;
LAST_DAY:
It produce last day of the given day from specified date.
----------------
SELECT
LAST_DAY(SYSDATE) FROM DUAL;
SELECT
LAST_DAY('06-FEB-2016') FROM DUAL;
EXTRACT:
This is used to extract the portion of the date value.
---------------
Syntax:
– EXTRACT ((year | month | day | hour | minute | second) FROM date);
--------
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
select extract (hour from cast(sysdate as
timestamp)) from dual
select extract (SECOND from cast(sysdate as
timestamp)) from dual;
SELECT EXTRACT( HOUR FROM interval '0 2:34'
day to minute) FROM DUAL;
SELECT EXTRACT( SECOND FROM interval '0
2:34:50' day to SECOND) FROM DUAL;
SELECT EXTRACT(HOUR FROM
NUMTODSINTERVAL(sysdate - trunc(sysdate), 'DAY')) FROM DUAL;
SELECT EXTRACT(MINUTE FROM
NUMTODSINTERVAL(sysdate - trunc(sysdate), 'DAY')) FROM DUAL;
SELECT EXTRACT(SECOND FROM
NUMTODSINTERVAL(sysdate - trunc(sysdate), 'DAY')) FROM DUAL;
NUMTODSINTERVAL converts n to an INTERVAL DAY
TO SECOND literal.
The argument n can be any NUMBER value or an
expression that can be implicitly converted to a NUMBER value.
The argument interval_unit can be of CHAR,
VARCHAR2, NCHAR, or NVARCHAR2 datatype.
The value for interval_unit specifies the
unit of n and must resolve to one of the following string values:
'DAY'
'HOUR'
'MINUTE'
'SECOND'
SQL> SELECT
NUMTODSINTERVAL(1,'DAY') FROM DUAL;
NUMTODSINTERVAL(1,'DAY')
---------------------------------------------------------------------
+000000001
00:00:00.000000000
SQL>
SELECT NUMTODSINTERVAL(1,'HOUR') FROM DUAL;
NUMTODSINTERVAL(1,'HOUR')
---------------------------------------------------------------------
+000000000
01:00:00.000000000
SQL>
SELECT NUMTODSINTERVAL(1,'MINUTE') FROM DUAL;
NUMTODSINTERVAL(1,'MINUTE')
---------------------------------------------------------------------
+000000000
00:01:00.000000000
SQL>
SELECT NUMTODSINTERVAL(1,'SECOND') FROM DUAL;
NUMTODSINTERVAL(1,'SECOND')
---------------------------------------------------------------------
+000000000
00:00:01.000000000
SQL>
SELECT NUMTODSINTERVAL(1,'MONTH') FROM DUAL;
SELECT
NUMTODSINTERVAL(1,'MONTH') FROM DUAL;
*
ERROR
at line 1:
ORA-01760:
illegal argument for function
select
to_char(sysdate, 'hh24:mi:ss') "Current Time",
2
extract(hour from to_timestamp(to_char(sysdate, 'dd-mon-yy
hh24:mi:ss'))) "Hour",
3
extract(minute from to_timestamp(to_char(sysdate, 'dd-mon-yy
hh24:mi:ss'))) "Minute",
4
extract(second from to_timestamp(to_char(sysdate, 'dd-mon-yy
hh24:mi:ss'))) "Second"
5
from dual;
Current Hour
Minute Second
--------
---------- ---------- ----------
12:40:02 12 40 2
=====================================================================================================================
GREATEST:
----------
SELECT GREATEST(DATE1,DATE2);
LEAST:
------
SELECT
LEAST(DATE1,DATE2);
ROUND:
ROUND will ROUNDs the date to which it was equal to or greater than the given
date.
------
Syntax:
– ROUND (date, (day | month | year))
• If the second parameter was year then
ROUND will checks the month of the given date in
The
following ranges.
JAN —
JUN
JUL —
DEC
• If the month falls between JAN and
JUN then it returns the first day of the current year.
• If the month falls between JUL and
DEC then it returns the first day of the next year.
EXAMPLE:
--------
SELECT
ROUND(TO_DATE('12-FEB-2016','DD-MON-YYYY'),'YEAR') FROM DUAL;
SELECT
ROUND(TO_DATE('12-AUG-2016','DD-MON-YYYY'),'YEAR') FROM DUAL;
MONTH:
-----
• If the day falls between 1 and 15
then it returns the first day of the current month.
• If the day falls between 16 and 31
then it returns the first day of the next month.
EXAMPLE:
--------
SELECT
ROUND(TO_DATE('12-AUG-2016','DD-MON-YYYY'),'MONTH') FROM DUAL;
SELECT
ROUND(TO_DATE('22-AUG-2016','DD-MON-YYYY'),'MONTH') FROM DUAL;
DAY:
----
• If the second parameter was day then
ROUND will checks the week day of the given date
• In the following ranges.
SUN —
WED
THU —
SUN
• If the week day falls between SUN and
WED then it returns the previous Sunday.
• If the weekday falls between THU and
SUN then it returns the next Sunday.
• If the second parameter was null then
it returns nothing.
• If we are not specifying the second
parameter then ROUND will resets the time to the beginning of the current day
in case of user specified date.
• If we are not specifying the second
parameter then ROUND will resets the time to the beginning of the next day in
case of SYSDATE.
TRUNC:
------
•TRUNC
will chops off the date to which it was equal to or less than the given date.
Syntax:
– TRUNC (date, (day | month | year))
• If the second parameter was year then
it always returns the first day of the current year.
• If the second parameter was month
then it always returns the first day of the current month.
• If the second parameter was day then
it always returns the previous Sunday.
• If the second parameter was null then
it returns nothing.
• If
you are not specifying the second parameter then trunk will resets the
time to the beginning of the current day.
CONVERSION FUNCTIONS:
----------------------------------------
Explicite
data type conversions:
-------------------------------------
sql
provides 3 functions to convert a value from one datatype to another datatype.
TO_CHAR()
TO_NUMBER()
TO_DATE()
TO_CHAR:
--------------
THE
TO_CHAR function will convert number or date to character string.
this
function can be used in to different flavours
TO_CHAR(NUMBER
CONVERSION TO CHARACTER CONVERSION)
TO_CHAR(DATE CONVERSION TO CHARACTER CONVERSION)
SYNTAX
FOR NUMBER TO CHARACTER CONVERSION:
------------------------------------------
TO_CHAR(NUMBER,fmt,nlsparms)
fmt:-it
is the optional number formate
nlsparms:-character
return by the number formate element.
1.DECIMAL
INDICATOR:(D)
---------------------
SELECT
20345,TO_CHAR(20345,99999D99) FROM DUAL;
2.SCIENTIFIC
NOTATION INDICATOR:(EEEE)
---------------------------------
SELECT
TO_CHAR(1234,'9999EEEE') FROM DUAL;
SELECT
TO_CHAR(123.34,'999.99EEEE') FROM DUAL;
SELECT
TO_CHAR(123.34,'999.9EEEE') FROM DUAL;
3.GROUP
SEPARATOR:(G)
------------------
SELECT
TO_CHAR(4500,4G500) FROM DUAL;
4.TRAILING
MINUS INDICATOR:(MI)
--------------------------------
SELECT
-9000,TO_CHAR(-9000,'9999MI') FROM DUAL;
5.NEGATIVE
NUMBER INDICATOR:(PR,PT)
-----------------------------------
SELECT
TO_CHAR(-9000,'9999PR') FROM DUAL;--here PR indicates the < >
SELECT
TO_CHAR(-9000,'9999PT') FROM DUAL;--here PT indicates the ( )
6.LOCAL
CURRENCY INDICATOR:(L)
-------------------------------
SELECT
TO_CHAR(4500,'999L') FROM DUAL;
7.GROUP
SEPARATOR:(,)
---------------------
SELECT
TO_CHAR(8400,'9,999) FROM DUAL;
8.DECIMAL
INDICATOR:(.)
-----------------------
SELECT
TO_CHAR(4500,'99.99') FROM DUAL;
9.DOLLAR
INDICATOR:($)
----------------------
SELECT
TO_CHAR(4500,'$9999) FROM DUAL;
SELECT
TO_CHAR(4500,'9999$') FROM DUAL;
10.ROMAN
NUMBER INDICATOR:(RN OR rn)
-----------------------------------
SELECT
TO_CHAR(45,'RN') FROM DUAL;
11.HEXA
DECIMAL INDICATOR:(X---XXXX)
--------------------------------------
SELECT
TO_CHAR(10,'X') FROM DUAL;
SELECT
TO_CHAR(11,'XX') FROM DUAL;
SELECT
TO_CHAR(12,'XX') FROM DUAL;
SELECT
TO_CHAR(13,'XX') FROM DUAL;
SELECT
TO_CHAR(123,'XXX') FROM DUAL;
12.ZERO
INDICATOR:(0)
--------------------
SELECT
TO_CHAR(9,'09') FROM DUAL;
SELECT
TO_CHAR(45,'990') FROM DUAL;
13.ISO
CURRENCY INDICATOR:(C)
----------------------------
SELECT
TO_CHAR(4560,'C9999') FROM DUAL;
DATE
TO CHARACTER CONVERSION:
----------------------------------------------------
1.AD
or A.D / BC or B.C INDICATOR: indicates with or without periods.
----------------------------------
SELECT
TO_CHAR(SYSDATE,'AD'),TO_CHAR(SYSDATE,'BC') FROM DUAL;
2.MERIDIAN
INDICATOR: (AM or A.M/PM or P.M)
-------------------------------------------
It
indicates with or without periods.
SELECT
TO_CHAR(SYSDATE,'AM'),TO_CHAR(SYSDATE,'PM') FROM DUAL;
3.CENTURY
INDICATOR:(CC/SCC)
----------------------------
it
indicates the century, S prefixes BC date with '-'
SELECT
TO_CHAR(SYSDATE,'CC-AD'),TO_CHAR(SYSDATE,'SCC-AD') FROM DUAL;
4.NUMERIC
WEEK DAY INDICATOR:(D-->1-7)
--------------------------------------
SELECT
TO_CHAR(SYSDATE,'D') FROM DUAL;
5.NUMERIC
MONTH INDICATOR:(MM)
-------------------------------
SELECT
TO_CHAR(SYSDATE,'MM') FROM DUAL;
6.NUMERIC
YEAR INDICATOR:(YYYY OR SYYYY):
----------------------------------------
7.MONTH
DAY INDICATOR:(DD-->1-31)
---------------------------------
8.YEAR
DAY INDICATOR:(DDD--->1-366)
----------------------------------
9.ABBREVATED
DAY INDICATOR:(DY)
---------------------------
SELECT
TO_CHAR(SYSDATE,'DD-DY-DAY') FROM DUAL;
10.WEE
DAY INDICATOR:(DAY)
--------------------------
11.ISO
STANDARD YEAR WEEK INDICATOR:(IW)
---------------------------------------
it spefies
the week of the year (1-52 or 1-53) based on ISO standard.
12.ISO
STANDARD 4 DIGIT INDICATOR:(IYYY)
------------------------------------------
can
be used combination of IYY,IY,I
13.FOUR
DIGIT YEAR INDICATOR:(YYYY or SYYYY)
------------------------------------------
14.SPELLED
YEAR INDICATOR:(YEAR or SYEAR)
---------------------------------------
15.WEEK
OF THE MONTH:(W--->1-5)
---------------------------------
16.YEAR
WEEK INDICATOR:(1-53 ANSII BASED WW)
--------------------------------------------
17.QUARTER
OF THE YEAR:(Q)
-------------------------
18.JULIAN
DAY INDICATOR:(J)
---------------------------
it is
number of days since jan1-4712BC
19.ABBREVATED
MONTH INDICATOR:(MON)
----------------------------------
20.MONTH
SPELLED INDICATOR:(MONTH)
----------------------------------
21.TWELVE HOUR CLOCK MODE:(HH or HH12)
---------------------------------
22.TWENTY
FOUR HOUR CLOCK MODE:(HH24)
--------------------------------------
23.MINUTES
INDICATOR:(MI)
24.SECONDS
INDICATOR:(SS)
-------------------------
25.DATE
FORMATE ELEMENT SUFFIXES:(TH or SP or RD or ND or ST)
---------------------------------------------------------------
========================================================================
TO_NUMBER:
TO_NUMBER(CHAR,fmt,nlsparms)
-------------------------------------
IT
CONVERTS CHARACTER INTO NUMBER
EX:TO_NUMBER('$1,000','L9G999')
FROM DUAL; --1000
EX:SELECT
TO_CHAR(TO_NUMBER(1,234.00,'9G999D99)+TO_NUMBER(2,346.89,'9,999.99),'9G999D99')RESULT
FROM DUAL;
TO_DATE:TO_DATE(CHAR,fmt,nlsparms)
----------------------------------
IT
converts CHAR TO CHAR or VARCHAR2 data type
SELECT
TO_DATE('12-JUN-2013','DD-MON-YYYY') FROM DUAL;
SELECT
TO_CHAR(TO_DATE('12-JUN-2013','DD-MON-YYYY'),'DDSP-MONTH-YEAR') FROM DUAL;
SELECT
'12-JUN-2013'+3 FROM DUAL;
SELECT
TO_CHAR(TO_DATE('12-DEC-2013','DD-MON-YYYY'),'DD-MONTH-DAY') FROM DUAL;
No comments:
Post a Comment