9. Date Manipulation

Determining Whether a Year Is a Leap Year

We want to determine whether the current year is a leap year.

Check the last day of February and if it is the 29th, then the current year is a leap year.

select to_char(
    last_day(add_months(trunc(sysdate,'y'),1)),
    'DD')
from t1

Determining the Number of Days in a Year

We want to count the number of days in the current year

select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual

Extracting Units of Time from a Date

We want to break the current date down into day, month, year, second, minute, and hour.

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

Determining the First and Last Days of a Month

We want to determine the first and last days for the current month

select trunc(sysdate,'mm') firstday,
    last_day(sysdate) lastday
from dual

Determining All Dates for a Particular Weekday Throughout a Year

We want to find all the dates in a year that correspond to a given day of the week. For example, generate a list of Fridays for the current year.

with x
as (
    select trunc(sysdate,'y')+level-1 dy
    from t1
    connect by level <=
    add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
)
select *
from x
where to_char( dy, 'dy') = 'fri'

Determining the Date of the First and Last Occurrences of a Specific Weekday in a Month

We want the first and last Mondays of the current month as an example.

select 
    next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday,
    next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday
from dual

Creating a Calendar

We want to create a calendar for the current month.

WITH x AS (
    SELECT
        *
    FROM
        (
            SELECT
                to_char(trunc(sysdate, 'mm') + level - 1,
                        'iw')          wk,
                to_char(trunc(sysdate, 'mm') + level - 1,
                        'dd')          dm,
                TO_NUMBER(to_char(trunc(sysdate, 'mm') + level - 1,
                                  'd'))                  dw,
                to_char(trunc(sysdate, 'mm') + level - 1,
                        'mm')          curr_mth,
                to_char(sysdate, 'mm') mth
            FROM
                dual
            CONNECT BY
                level <= 31
        )
    WHERE
        curr_mth = mth
)
SELECT
    MAX(
        CASE dw
            WHEN 2 THEN
                dm
        END
    ) mo,
    MAX(
        CASE dw
            WHEN 3 THEN
                dm
        END
    ) tu,
    MAX(
        CASE dw
            WHEN 4 THEN
                dm
        END
    ) we,
    MAX(
        CASE dw
            WHEN 5 THEN
                dm
        END
    ) th,
    MAX(
        CASE dw
            WHEN 6 THEN
                dm
        END
    ) fr,
    MAX(
        CASE dw
            WHEN 7 THEN
                dm
        END
    ) sa,
    MAX(
        CASE dw
            WHEN 1 THEN
                dm
        END
    ) su
FROM
    x
GROUP BY
    wk
ORDER BY
    wk

Listing Quarter Start and End Dates for the Year

We want to return the start and end dates for each of the four quarters of a given 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

Determining Quarter Start and End Dates for a Given Quarter

We want to return the quarter’s start and end dates, for a given year and quarter in the format of YYYYQ (four-digit year, one-digit quarter).

select add_months(q_end,-2) q_start,
last_day(q_end) q_end
from (
    select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
    from (
        select 20051 yrq from dual union all
        select 20052 yrq from dual union all
        select 20053 yrq from dual union all
        select 20054 yrq from dual
     ) x
 ) y

Searching on Specific Units of Time

We want to find all employees hired in February or December, as well as employees hired on a Tuesday.

select ename
from emp
where rtrim(to_char(hiredate,'month')) in ('february','december')
or rtrim(to_char(hiredate,'day')) = 'tuesday'

Comparing Records Using Specific Parts of a Date

We want to find which employees have been hired on the same month and weekday. For example, if an employee was hired on Monday, March 10, 2008, and another employee was hired on Monday, March 2, 2001, we want those two to come up as a match since the day of week and month match.

select a.ename ||
    ' was hired on the same month and weekday as '||
    b.ename as msg
from emp a, emp b
where to_char(a.hiredate,'DMON') = to_char(b.hiredate,'DMON')
and a.empno < b.empno
order by a.ename

Identifying Overlapping Date Ranges

We want to find all instances of an employee starting a new project before ending an existing project.

select a.empno,a.ename,
  'project '||b.proj_id||
  ' overlaps project '||a.proj_id as msg
from emp_project a, emp_project b
where a.empno = b.empno
  and b.proj_start >= a.proj_start
  and b.proj_start <= a.proj_end
  and a.proj_id != b.proj_id