9. Date Manipulation
Determining Whether a Year Is a Leap Year
We want to determine whether the current year is a leap year.
select to_char(
last_day(add_months(trunc(sysdate,'y'),1)),
'DD')
from t1Determining 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 dualExtracting 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 dualDetermining 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 dualCreating 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.enameIdentifying 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_idLast updated