Date Functions in Oracle SQL
3shTech (trainings@trishtechnology.com)
Date Functions
· Oracle default date format is DD-MON-YYYY.
· We can change the default format to our
desired format by using the following command.
But this will expire once the session was closed.
SQL>ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MONTH-YYYY’;
Below are the Oracle DATE FUNCTIONS used to manipulate the date values:-
· 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
· This will give the current date and time.
Example:–
SQL>SELECT SYSDATE FROM DUAL;
· This will returns the current date in the
session’s time zone.
Example:–
SQL>SELECT CURRENT_DATE FROM DUAL;
· This will returns the current timestamp with
the active time zone information.
Example:–
SQL>SELECT CURRENT_TIMESTAMP FROM DUAL;
· This will returns the system date, including
fractional seconds and time zone of the Database.
Example:–
SQL>SELECT SYSTIMESTAMP FROM DUAL;
· This will returns local timestamp in the
active time zone information, with no time zone information shown.
Example:–
SQL>SELECT LOCALTIMESTAMP FROM DUAL;
· This will returns the current database time
zone in UTC format. (Coordinated Universal Time).
Example:–
SQL>SELECT DBTIMEZONE FROM DUAL;
· This will return the value of the current
session’s time zone.
Example:–
SQL>SELECT SESSIONTIMEZONE FROM DUAL;
· This will be used to EXTRACT various date
formats.
· The available date formats as follows.
Syntax: – TO_CHAR (date, format)
DATE FORMATS:
|
Date Format |
Description |
|
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 letters 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 |
Examples:–
SQL>SELECT TO_CHAR (SYSDATE,’dd-mm-yyyy hh24: mm:ss’) FROM DUAL;
SQL>SELECT TO_CHAR (SYSDATE,’dd month year’) FROM DUAL;
· This will be used to convert the string into
date format.
Syntax: – TO_DATE (date)
Example:–
SQL>SELECT TO_CHAR (TO_DATE (’24/dec/2006′,’dd/mon/yyyy’), ‘dd * month
* day’) date_format FROM DUAL;
— If you are not using TO_CHAR oracle will display output in
default date format.
ADD_MONTHS:
· This will add the specified months to the
given date.
Syntax: – ADD_MONTHS (date, no_of_months)
Example:–
SQL>SELECT ADD_MONTHS (SYSDATE, 5) FROM DUAL;
SQL>SELECT ADD_MONTHS (SYSDATE,-5) FROM DUAL;
<img alt=""
src="http://newtonapples.com/old/Old/wp-content/uploads/2012/09/092112_1802_DateFunctio12.png"
/>
<ul>
<li>If no_of_months is zero then it will display the same
date.</li>
</ul>
SQL>SELECT ADD_MONTHS (SYSDATE, 0) FROM DUAL;
· If no_of_months is null then it will display
nothing.
SQL>SELECT ADD_MONTHS (SYSDATE, null) FROM DUAL;
· This will give difference of months between
two dates.
Syntax: – MONTHS_BETWEEN (date1, date2)
Example:–
SQL>SELECT MONTHS_BETWEEN (ADD_MONTHS (SYSDATE, 5), SYSDATE) FROM
DUAL;
SQL>SELECT MONTHS_BETWEEN (TO_DATE (’11-aug-1990′,’dd-mon-yyyy’),
TO_DATE (’11- jan-1990′,’dd-mon-yyyy’)) mon_bet FROM DUAL;
· This will produce next day of the given day
FROM the specified date.
Syntax: – NEXT_DAY (date, day)
Example:–
SQL>SELECT NEXT_DAY (SYSDATE,’TUE’) FROM DUAL;
· This will produce last day of the given date.
Syntax: – LAST_DAY (date)
Example:–
SQL>SELECT LAST_DAY (SYSDATE) FROM DUAL;
· This is used to EXTRACT a portion of the date
value.
Syntax: – EXTRACT ((year | month | day | hour |
minute | second) FROM date)
Example:–
SQL>SELECT EXTRACT (year FROM SYSDATE) FROM DUAL;
SQL>SELECT EXTRACT (month FROM SYSDATE) FROM DUAL;
SQL>SELECT EXTRACT (day FROM SYSDATE) FROM DUAL;
· This will give the GREATEST date.
· It will take ‘n’ number of arguments but data
type should be same.
Syntax: – GREATEST (date1, date2….daten)
Example:–
SQL>SELECT GREATEST (TO_DATE (’11-jan-1990′,’dd-mon-yyyy’), TO_DATE
(’11-mar-1990′,’dd-mon-yyyy’), TO_DATE (’11-apr-[/sql]
[sql] 1990′,’dd-mon-yyyy’)) FROM DUAL;
· If all the values are zeros then it will
display zero.
· If all the parameters are nulls then it will
display nothing.
· If any of the parameters is null it will
display nothing.
· This will give the LEAST date.
· It will take ‘n’ number of arguments but data
type should be same.
Syntax: – LEAST (date1, date2….daten)
Example:–
SQL>SELECT
LEAST(TO_DATE(’11-jan-1990′,’dd-mon-yyyy’),TO_DATE(’11-mar-1990′,’dd-mon-yyyy’),TO_DATE(’11-apr-1990′,’dd-mon-yyyy’))
least_date FROM DUAL;
· If all the values are zeros then it will
display zero.
· If all the parameters are nulls then it will
display nothing.
· If any of the parameters is null it will
display nothing.
· 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 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 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.
Example:–
SQL>SELECT ROUND (TO_DATE (’24-dec-04′,’dd-mon-yy’),’year’), ROUND
(TO_DATE (’11-mar-06′,’dd-mon-yy’),’year’) FROM DUAL;
SQL>SELECT ROUND (TO_DATE (’11-jan-04′,’dd-mon-yy’),’month’), ROUND
(TO_DATE (’18-jan-04′,’dd-mon-yy’),’month’) FROM DUAL;
SQL>SELECT ROUND (TO_DATE (’26-dec-06′,’dd-mon-yy’),’day’), ROUND
(TO_DATE (’29-dec-06′,’dd-mon-yy’),’day’) FROM DUAL;
· 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.
SQL>SELECT TRUNC (TO_DATE (’24-dec-04′,’dd-mon-yy’),’year’), TRUNC (TO_DATE (’11-mar-06′,’dd-mon-yy’),’year’) FROM DUAL;
SQL>SELECT TRUNC (TO_DATE (’11-jan-04′,’dd-mon-yy’),’month’), TRUNC (TO_DATE (’18-jul-04′,’dd-mon-yy’),’month’) FROM DUAL;
SQL>SELECT TRUNC (TO_DATE (’26-dec-06′,’dd-mon-yy’),’day’), TRUNC
(TO_DATE (’29-dec-06′,’dd-mon-yy’),’day’) FROM DUAL;
· This will give the desired time zone’s date
and time.
Syntax: NEW_TIME (date, current_timezone,
desired_timezone)
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.
Example:-
SQL>SELECT TO_CHAR (NEW_TIME (SYSDATE,’gmt’,’yst’),’dd mon yyyy hh: mi:
ss am’) FROM DUAL;
SQL>SELECT TO_CHAR (NEW_TIME (SYSDATE,’gmt’,’est’),’dd mon yyyy hh: mi:
ss am’) FROM DUAL;
· This will give the first non-null date.
Syntax: – COALESCE (date1, date2, date3….daten)
Example:–
SQL>SELECT COALESCE (’12-jan-90′,’13-jan-99′), COALESCE
(null,’12-jan-90′,’23-mar-98′, null) FROM DUAL;
Comments
Post a Comment