4

How to Create a View with all days in year. view should fill with dates from JAN-01 to Dec-31. How can I do this in Oracle ?

If current year have 365 days,view should have 365 rows with dates. if current year have 366 days,view should have 366 rows with dates. I want the view to have a single column of type DATE.

Bishan
  • 15,211
  • 52
  • 164
  • 258
  • 1
    Do you want a view with 365 rows? Or a view with every day in a particular year? Because of leap years, some years have 366 days. Do you want the view to have a single column of type `DATE`, in which case we'd need to know what particular year you want the rows to be from and whether you want that to change so that the view always returns the data for the current year, for example. Or do you want to return a string representation of a day? – Justin Cave Dec 16 '11 at 07:57
  • if current year have 365 days,`view` has 365 rows with dates. if current year have 366 days,`view` has 366 rows with dates. i want the view to have a single column of type `DATE` – Bishan Dec 16 '11 at 08:13

3 Answers3

11

This simple view will do it:

create or replace view year_days as
select trunc(sysdate, 'YYYY') + (level-1) as the_day
from dual
connect by level <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'))
/

Like this:

SQL> select * from year_days;

THE_DAY
---------
01-JAN-11
02-JAN-11
03-JAN-11
04-JAN-11
05-JAN-11
06-JAN-11
07-JAN-11
08-JAN-11
09-JAN-11
10-JAN-11
11-JAN-11

...

20-DEC-11
21-DEC-11
22-DEC-11
23-DEC-11
24-DEC-11
25-DEC-11
26-DEC-11
27-DEC-11
28-DEC-11
29-DEC-11
30-DEC-11
31-DEC-11

365 rows selected.

SQL>

The date is generated by applying several Oracle date functions:

  • trunc(sysdate, 'yyyy') gives us the first of January for the current year
  • add_months(x, 11) gives us the first of December
  • last_day(x) gives us the thirty-first of December
  • to_char(x, 'DDD') gives us the number of the thirty-first of December, 365 this year and 366 next.
  • This last figure provides the upper bound for the row generator CONNECT BY LEVEL <= X
APC
  • 144,005
  • 19
  • 170
  • 281
0

This works well in MS SQL

SELECT TOP (DATEDIFF(day,  DATEADD(yy, DATEDIFF(yy,0,getdate()), 0), DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1))) n = ROW_NUMBER() OVER (ORDER BY [object_id]), 
dateadd(day, ROW_NUMBER() OVER (ORDER BY [object_id]) - 1, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) AS AsOfDate FROM   sys.all_objects
Brandon Johnson
  • 329
  • 5
  • 11
0

you can use piplined table, it should be something like this:

create or replace type year_date_typ as object (v_day date);
create or replace type year_date_tab as table of year_date_typ;

CREATE OR REPLACE FUNCTION get_dates(year IN VARCHAR2) RETURN year_date_tab PIPELINED IS
v_start_date date := to_date('0101' || year, 'ddmmyyyy');
res year_date_typ := year_date_typ(null);
v_days_in_year integer := 365;
BEGIN
if to_char(last_day(to_date('0102'||year, 'ddmmyyyy')), 'dd') = '29' then
v_days_in_year := 366;
end if;
FOR i in 0 .. v_days_in_year integer-1 LOOP
res.v_day := v_start_date + i;
pipe row(res);
END LOOP;

return;
END get_dates;

and you can use it:

select * from table(get_dates('2011'));
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • got this error PLS-00653: aggregate/table functions are not allowed in PL/SQL scope – Bishan Dec 16 '11 at 08:50
  • well, my code may not be perfect (I can't check it), but I still think that the approach of having a piplined function is better, since it allows you to get the year as a parameter – A.B.Cade Dec 16 '11 at 11:57