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