DY -- Three letter abbreviated day
DAY -- Fully spelled out day
Y -- Last one digit of the year
YY -- Last two digits of the year
YYY -- Last three digits of the year
YYYY -- Full four digit year
SYYYY -- Signed
year
I -- One digit year from ISO standard
IY -- Two digit year from ISO standard
IYY -- Three digit year from ISO standard
IYYY -- Four digit year from ISO standard
Y, YYY -- Year with comma
YEAR -- Fully spelled out year
CC -- Century
Q -- No of quarters
W -- No of weeks in month
WW --
No of weeks in year
IW -- No of weeks in year from ISO standard
HH -- Hours
MI -- Minutes
SS -- Seconds
FF -- Fractional seconds
AM or PM -- Displays AM or PM depending upon time
of day
A.M or P.M -- Displays A.M or P.M depending upon time
of day
AD or BC -- Displays AD or BC depending upon the
date
A.D or B.C -- Displays AD or BC depending upon the
date
FM -- Prefix to month or day, suppresses
padding of month or day
TH -- Suffix to a number
SP -- suffix to a number to be spelled out
SPTH -- Suffix combination of TH and SP to be
both spelled out
THSP -- same as SPTH
Ex:
SQL> select to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from
dual;
TO_CHAR(SYSDATE,'DD MONTH YYYYHH:MI
----------------------------------------------------
24 december 2006
02:03:23 pm sun
SQL> select to_char(sysdate,'dd month year') from dual;
TO_CHAR(SYSDATE,'DDMONTHYEAR')
-------------------------------------------------------
24 december two
thousand six
SQL> select to_char(sysdate,'dd fmmonth
year') from dual;
TO_CHAR(SYSDATE,'DD FMMONTH YEAR')
-------------------------------------------------------
24 december two thousand six
SQL> select to_char(sysdate,'ddth
DDTH') from dual;
TO_CHAR(S
------------
24th 24TH
SQL> select to_char(sysdate,'ddspth
DDSPTH') from dual;
TO_CHAR(SYSDATE,'DDSPTHDDSPTH
------------------------------------------
twenty-fourth TWENTY-FOURTH
SQL> select to_char(sysdate,'ddsp Ddsp
DDSP ') from dual;
TO_CHAR(SYSDATE,'DDSPDDSPDDSP')
------------------------------------------------
twenty-four Twenty-Four TWENTY-FOUR
i) TO_DATE
This will be used to convert the string
into data format.
Syntax:
to_date (date)
Ex:
SQL> select
to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day')
from dual;
TO_CHAR(TO_DATE('24/DEC/20
--------------------------
24 * december *
Sunday
-- If you are not using to_char oracle will display output
in default date format.
j) ADD_MONTHS
This will add the specified months to the
given date.
Syntax:
add_months (date, no_of_months)
Ex:
SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), 5) from
dual;
ADD_MONTHS
----------------
11-JUN-90
SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), -5)
from dual;
ADD_MONTH
---------------
11-AUG-89
1
If no_of_months is zero then it will display the same date.
2
If no_of_months is null then it will display nothing.
k) MONTHS_BETWEEN
This will give difference of months between
two dates.
Syntax:
months_between (date1, date2)
Ex:
SQL> select
months_between(to_date('11-aug-1990','dd-mon-yyyy'), to_date('11-
jan-1990','dd-mon-yyyy')) from
dual;
MONTHS_BETWEEN(TO_DATE('11-AUG-1990','DD-MON-YYYY'),TO_DATE('11-JAN-1990','DD-MON-YYYY'))
-----------------------------------------------------------------------------------------------
7
SQL> select
months_between(to_date('11-jan-1990','dd-mon-yyyy'), to_date('11-
aug-1990','dd-mon-yyyy')) from
dual;
MONTHS_BETWEEN(TO_DATE('11-JAN-1990','DD-MON-YYYY'),TO_DATE('11-AUG-1990','DD-MON-YYYY'))
-------------------------------------------------------------------------------------------------
-7
l) NEXT_DAY
This will produce next day of the given day
from the specified date.
Syntax:
next_day (date, day)
Ex:
SQL> select next_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun')
from dual;
NEXT_DAY(
-------------
31-DEC-06
-- If the day parameter is null then it will display
nothing.
m) LAST_DAY
This will produce last day of the given
date.
Syntax:
last_day (date)
Ex:
SQL> select last_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun')
from dual;
LAST_DAY(
-------------
31-DEC-06
n) EXTRACT
This is used to extract a portion of the
date value.
Syntax:
extract ((year | month | day | hour | minute | second), date)
Ex:
SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------------------
2006
-- You can extract only one value at a time.
o) GREATEST
This will give the greatest date.
Syntax:
greatest (date1, date2, date3 … daten)
Ex:
SQL> select
greatest(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-
mon-yy'),to_date('11-apr-90','dd-mon-yy')) from dual;
GREATEST(
-------------
11-APR-90
p) LEAST
This will give the least date.
Syntax:
least (date1, date2, date3 … daten)
Ex:
SQL> select
least(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-mon-
yy'),to_date('11-apr-90','dd-mon-yy')) from dual;
LEAST(
-------------
11-JAN-90
q) 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.
If the second parameter was month
then round will checks the day of the given date in
the following ranges.
1 -- 15
16 -- 31
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.
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
No comments:
Post a Comment