Important Date Related Queries - 1

[debasisdas]

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