> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/9.-date-manipulation.md).

# 9. Date Manipulation

## Determining Whether a Year Is a Leap Year

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

{% hint style="info" %}
Check the last day of February and if it is the 29th, then the current year is a leap year.
{% endhint %}

```
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
```

<figure><img src="/files/4LoYsLRbZCO1pCQBq84X" alt="" width="292"><figcaption></figcaption></figure>

## 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
```

<figure><img src="/files/pIJMMGmQqIgZJh4yiwFD" alt="" width="354"><figcaption></figcaption></figure>

## 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
```

<figure><img src="/files/GPSgtd00GWk9sA41NljD" alt="" width="197"><figcaption></figcaption></figure>

## 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
```

<figure><img src="/files/Otfs0VJEFIGIyCta2bC4" alt="" width="212"><figcaption></figcaption></figure>

## 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
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/9.-date-manipulation.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.