This article consistes of some of the frequently asked date related queries.
Hope the users find it useful.
==========================
1.Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
------------------------------------
- Select next_day(trunc(sysdate,'mm')-1,'SUNDAY') First_Sunday, next_day(last_day(trunc(sysdate,'mm'))-7,'SUNDAY') Last_Sunday from dual
For other days please modify the name of the DAY in the query.
2.Determining the First and Last Day of a Month
------------------------------------------------------------------------
- select trunc(sysdate,'mm') Firstday,
-
last_day(sysdate) Lastday from dual
3.Extracting Units of Time from a Date
------------------------------------------------------------
- Select to_number(to_char(sysdate,'hh24')) hour,
-
to_number(to_char(sysdate,'mi')) min,
-
to_number(to_char(sysdate,'ss')) sec,
-
to_number(to_char(sysdate,'dd')) day,
-
to_number(to_char(sysdate,'mm')) mth,
-
to_number(to_char(sysdate,'yyyy')) year
-
from dual
4.To find out No of days in year.
---------------------------------
- select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual
5.To find out if the year is a leap Year or not.
---------------------------------------------------------------------
- select decode(to_char(last_day(add_months(trunc(sysdate,'y'),1)),'DD'),28 ,'Not a Leap Year','Leap Year') from dual
6.Listing Quarter Start and End Dates for the Year
-------------------------------------------------------------------------
- Select rownum qtr,
-
add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start, add_months(trunc(sysdate,'y'),rownum*3)-1 q_end from emp where rownum <= 4
7.Retrivinging three most recent dates from a table
=================================
- select e.hiredate from
-
(
-
select dense_rank() over (order by hiredate desc)n,hiredate
-
from emp )e where e.n<=3
8.Determining the Number of Months or Years Between Two Dates
---------------------------------------------------------------------------------------------------
- select months_between(max_hd,min_hd) MONTHS,
-
months_between(max_hd,min_hd)/12 YEARS
-
from (select min(hiredate) min_hd, max(hiredate) max_hd from emp ) x
9.Determining the Number of Seconds, Minutes, or Hours Between Two Dates
------------------------------------------------------------------------------------------------------------------
- select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec from (select (max(case when ename = 'WARD' then hiredate end) -
-
max(case when ename = 'ALLEN' then hiredate end)) as dy from emp) x
10.Determining the Date Difference Between the Current Record and the Next Record
-----------------------------------------------------------------------------------------------------------------
- select ename, hiredate, next_hd,
-
next_hd - hiredate diff from (
-
select deptno, ename, hiredate, lead(hiredate)over(order by hiredate) next_hd
-
from emp) ORDER BY HIREDATE
11.Counting the Occurrences of Weekdays in a Year
--------------------------------------------------------------------------------
- with x as ( select level lvl from dual connect by level <= ( add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y'))
-
) select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*) from x group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')
Also check
Important Date Related Queries - 2 
