- --Find the dates for the given year using the query in Oracle
- --Find dates between given date ranges, the same query will be used just tweak the cal_year
- --Just place year in the given: CAL_YEAR and execute the query to get your result set
SELECT daterange,
dayname
FROM (SELECT ( To_date(01
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(01
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(01
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(01
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(02
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(02
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(02
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(02
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(03
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(03
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(03
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(03
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(04
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(04
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(04
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(04
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(05
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(05
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(05
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(05
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(06
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(06
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(06
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(06
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(07
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(07
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(07
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(07
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(08
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(08
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(08
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(08
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(09
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(09
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(09
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(09
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(10
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(10
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(10
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(10
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(11
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(11
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(11
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(11
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31
UNION
SELECT ( To_date(12
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL )
DateRange,
To_char(To_date(12
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY')
DAYNAME
FROM dual
WHERE ( To_date(12
||' '
||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day(
To_date(12
||' '
||:CAL_YEAR, 'MM YYYY'))
CONNECT BY LEVEL <= 31);