DATE FUNCTIONS
Ø Sysdate
Ø Current_date
Ø Current_timestamp
Ø Systimestamp
Ø Localtimestamp
Ø Dbtimezone
Ø Sessiontimezone
Ø To_char
Ø To_date
Ø Add_months
Ø Months_between
Ø Next_day
Ø Last_day
Ø Extract
Ø Greatest
Ø Least
Ø Round
Ø Trunc
Ø New_time
Ø Coalesce
Oracle default date format is DD-MON-YY.
We can change the default format to our desired format by using
the following command.
SQL> alter session set nls_date_format = ‘DD-MONTH-YYYY’;
But this will expire
once the session was closed.
a) SYSDATE
This will give the
current date and time.
Ex:
SQL> select sysdate from dual;
SYSDATE
-----------
24-DEC-06
b) CURRENT_DATE
This will returns the
current date in the session’s timezone.
Ex:
SQL> select current_date from
dual;
CURRENT_DATE
------------------
24-DEC-06
c) CURRENT_TIMESTAMP
This will returns the
current timestamp with the active time zone information.
Ex:
SQL> select current_timestamp
from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.42.41.383369
AM +05:30
d) SYSTIMESTAMP
This will returns the
system date, including fractional seconds and time zone of the
database.
Ex:
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.49.31.830099
AM +05:30
e) LOCALTIMESTAMP
This will returns local
timestamp in the active time zone information, with no time
zone information shown.
Ex:
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.44.18.502874 AM
f) DBTIMEZONE
This will returns the current database time
zone in UTC format. (Coordinated Universal
Time)
Ex:
SQL> select dbtimezone from dual;
DBTIMEZONE
---------------
-07:00
g) SESSIONTIMEZONE
This will returns the
value of the current session’s time zone.
Ex:
SQL> select sessiontimezone
from dual;
SESSIONTIMEZONE
------------------------------------
+05:30
h) TO_CHAR
This will be used to
extract various date formats.
The available date
formats as follows.
Syntax: to_char (date, format)
DATE FORMATS
D -- No
of days in week
DD -- No
of days in month
DDD -- No
of days in year
MM -- No
of month
MON -- Three
letter abbreviation of month
MONTH -- Fully
spelled out month
RM -- Roman
numeral month
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
Ø If no_of_months is zero then it will display the same date.
Ø 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
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 the you are not
specifying the second parameter then round will resets the time to the begining
of the current day in case of user specified date.
Ø If the you are not
specifying the second parameter then round will resets the time to the begining
of the next day in case of sysdate.
Ex:
SQL> select
round(to_date('24-dec-04','dd-mon-yy'),'year'), round(to_date('11-mar-
06','dd-mon-yy'),'year') from dual;
ROUND(TO_ ROUND(TO_
------------ ---------------
01-JAN-05 01-JAN-06
SQL> select
round(to_date('11-jan-04','dd-mon-yy'),'month'), round(to_date('18-
jan-04','dd-mon-yy'),'month') from dual;
ROUND(TO_ ROUND(TO_
------------- ---------------
01-JAN-04 01-FEB-04
SQL> select
round(to_date('26-dec-06','dd-mon-yy'),'day'), round(to_date('29-dec-
06','dd-mon-yy'),'day') from dual;
ROUND(TO_ ROUND(TO_
-------------- --------------
24-DEC-06 31-DEC-06
SQL> select
to_char(round(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy
hh:mi:ss
am') from dual;
TO_CHAR(ROUND(TO_DATE('
---------------------------------
24 dec 2006 12:00:00 am
r) 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 the you are not
specifying the second parameter then trunk will resets the time to the begining
of the current day.
Ex:
SQL> select
trunc(to_date('24-dec-04','dd-mon-yy'),'year'), trunc(to_date('11-mar-
06','dd-mon-yy'),'year') from dual;
TRUNC(TO_ TRUNC(TO_
------------- --------------
01-JAN-04 01-JAN-06
SQL> select
trunc(to_date('11-jan-04','dd-mon-yy'),'month'), trunc(to_date('18-jan-
04','dd-mon-yy'),'month') from dual;
TRUNC(TO_ TRUNC(TO_
------------- -------------
01-JAN-04 01-JAN-04
SQL> select trunc(to_date('26-dec-06','dd-mon-yy'),'day'),
trunc(to_date('29-dec-
06','dd-mon-yy'),'day') from dual;
TRUNC(TO_ TRUNC(TO_
------------- --------------
24-DEC-06 24-DEC-06
SQL> select to_char(trunc(to_date('24-dec-06','dd-mon-yy')),
'dd mon yyyy hh:mi:ss
am') from
dual;
TO_CHAR(TRUNC(TO_DATE('
---------------------------------
24 dec 2006 12:00:00 am
s) NEW_TIME
This will give the desired timezone’s date and
time.
Syntax: new_time (date, current_timezone, desired_timezone)
Available
timezones are as follows.
TIMEZONES
AST/ADT -- Atlantic
standard/day light time
BST/BDT -- Bering
standard/day light time
CST/CDT -- Central
standard/day light time
EST/EDT -- Eastern
standard/day light time
GMT -- Greenwich mean time
HST/HDT -- Alaska-Hawaii
standard/day light time
MST/MDT -- Mountain
standard/day light time
NST -- Newfoundland
standard time
PST/PDT -- Pacific
standard/day light time
YST/YDT -- Yukon standard/day light
time
Ex:
SQL> select
to_char(new_time(sysdate,'gmt','yst'),'dd mon yyyy hh:mi:ss am') from
dual;
TO_CHAR(NEW_TIME(SYSDAT
-----------------------------------
24 dec 2006 02:51:20 pm
SQL> select
to_char(new_time(sysdate,'gmt','est'),'dd mon yyyy hh:mi:ss am') from
dual;
TO_CHAR(NEW_TIME(SYSDAT
-----------------------
24 dec 2006 06:51:26 pm
t) COALESCE
This will give the first
non-null date.
Syntax: coalesce (date1, date2, date3 … daten)
Ex:
SQL> select
coalesce('12-jan-90','13-jan-99'), coalesce(null,'12-jan-90','23-mar-
98',null)
from dual;
COALESCE( COALESCE(
------------- ------------
12-jan-90 12-jan-90
No comments:
Post a Comment