One Stop Shopping for Date/Timestamp Queries

Assumptions

[1] Under this work, ALL_OBJECTS view will be used, as it usually has many rows.
[2] New queries will be added to address new requirements.This post will grow with time.
[3] Reader should have basic knowledge of built-in SQL functions.

[4] Following are not the only ways to solve the questions.

Queries

1) Given a date, Display n subsequent days including days name ?

[Solution]

Given date='28-AUG-01' and n=8

SQL> select to_char(SUB_DATE,'Day') Day,SUB_DATE SUBSEQUENT_DATE from
(select to_date('28-AUG-01') + rownum-1 SUB_DATE
from ALL_OBJECTS where rownum<=8)


DAY SUBSEQUENT_DATE
--------- ---------
Tuesday 28-AUG-01
Wednesday 29-AUG-01
Thursday 30-AUG-01
Friday 31-AUG-01
Saturday 01-SEP-01
Sunday 02-SEP-01
Monday 03-SEP-01
Tuesday 04-SEP-01

8 rows selected.

SQL>

2) Given two dates, Provide days between them including days name ?

[Solution]

Given dates='28-JAN-01','05-FEB-01'

SQL> select
to_char(BETWEEN_DATES,'Day') Day, BETWEEN_DATES DATES from
(
select to_date('28-JAN-01')+rownum-1 BETWEEN_DATES from ALL_OBJECTS
where rownum<=(to_date('05-FEB-01')-to_date('28-JAN-01')+1)
)
/

DAY DATES
--------- ---------
Sunday 28-JAN-01
Monday 29-JAN-01
Tuesday 30-JAN-01
Wednesday 31-JAN-01
Thursday 01-FEB-01
Friday 02-FEB-01
Saturday 03-FEB-01
Sunday 04-FEB-01
Monday 05-FEB-01

9 rows selected.

SQL>

3) How many sundays came in the month of december 2006?

[Solution]

SQL> select count(*) sundays from
(
select to_char(to_date('01-DEC-06')+rownum-1,'Day') MONTH_DAYS
from ALL_OBJECTS
where rownum<=(last_day(to_date('01-DEC-06'))-to_date('01-DEC-06')+1)
)
where trim(MONTH_DAYS)='Sunday'
/

SUNDAYS
----------
5

SQL>

4) Fetch the data from a table for the current month and the next two months.

[Solution]

SQL> desc xxx;
Name Null? Type
----------------------------------------- -------- ----------------------------

DATE1 DATE

SQL> select * from xxx;
DATE1
---------
15-MAR-07
13-MAR-07
13-APR-07
29-MAY-07
29-JUN-07
29-JAN-07

SQL> select sysdate from dual;

SYSDATE
----------
27-MAR-07

SQL> select * from xxx
where date1
between trunc(sysdate,'mm') and
trunc(add_months(sysdate,3),'mm')-1;

DATE1
---------
15-MAR-07
13-MAR-07
13-APR-07
29-MAY-07

SQL>

New queries will come very soon...