SQL & PL/SQL

select 
   trunc(input_date, 'YYYY')  as the_year,
   add_months(trunc(input_date, 'YYYY'), 12) 
   -
   trunc(input_date, 'YYYY')  as days_in_year   
from
(
   select 
      add_months(sysdate, -12 * level) as input_date
   from dual
   connect by level <= 20
 12  );

THE_YEAR	     DAYS_IN_YEAR
-------------------- ------------
01-JAN-2009 12 00:00	      365
01-JAN-2008 12 00:00	      366
01-JAN-2007 12 00:00	      365
01-JAN-2006 12 00:00	      365
01-JAN-2005 12 00:00	      365
01-JAN-2004 12 00:00	      366
01-JAN-2003 12 00:00	      365
01-JAN-2002 12 00:00	      365
01-JAN-2001 12 00:00	      365
01-JAN-2000 12 00:00	      366
01-JAN-1999 12 00:00	      365
01-JAN-1998 12 00:00	      365
01-JAN-1997 12 00:00	      365
01-JAN-1996 12 00:00	      366
01-JAN-1995 12 00:00	      365
01-JAN-1994 12 00:00	      365
01-JAN-1993 12 00:00	      365
01-JAN-1992 12 00:00	      366
01-JAN-1991 12 00:00	      365
01-JAN-1990 12 00:00	      365

20 rows selected.

TUBBY_TUBBZ?

The above shows the number of days in a year for the past 20 years.

So assuming you had a variable .. called "input_date"

   trunc(input_date, 'YYYY')  as the_year,
   add_months(trunc(input_date, 'YYYY'), 12) 
   -
   trunc(input_date, 'YYYY')  as days_in_year   

Would do it.

Are you referring to the number of processing days (from monday up to friday, just for example) or the actual number of days in a year?

SQL> ed
Wrote file afiedt.buf

  1* select add_months(trunc(sysdate,'YYYY'),12) -trunc(sysdate,'YYYY') cnt from dual
SQL> /

       CNT
----------
       365

OR

select to_date('01-JAN-'||to_char(to_number(to_char(sysdate,'YYYY'))+1),'DD-MM-YYYY')-trunc(sysdate,'YYYY') cnt
from dual
/

SQL> /

       CNT
----------
       365

You can replace sysdate with your processing date

Hi,

Here is Tubby's solution using to_char to extract the year from the date and hopefully make the output a tad more readable (only the year is displayed on the first column instead of a full date) (just splitting hairs ;) )

select
   to_char(trunc(input_date, 'YYYY'), 'YYYY')  as "Year",
   add_months(trunc(input_date, 'YYYY'), 12)
   -
   trunc(input_date, 'YYYY')  as days_in_year
from
(
   select
      add_months(sysdate, -12 * level) as input_date
   from dual
   connect by level <= 20
)
order by "Year" desc
;

Year DAYS_IN_YEAR
---- ------------
2009          365
2008          366
2007          365
2006          365
2005          365
2004          366
2003          365
2002          365
2001          365
2000          366
1999          365

Year DAYS_IN_YEAR
---- ------------
1998          365
1997          365
1996          366
1995          365
1994          365
1993          365
1992          366
1991          365
1990          365

John.

Edited by: 440bx - 11gR2 on Aug 1, 2010 9:40 PM - Bolded important information ;)

Oh, it's not that important :)

you're right... just important enough :D

Thanks everyone.

CD

I like to_char ddd format :8}

select
to_number(to_char(trunc(sysdate,'yyyy')-1+InterVal '1' year,'ddd')) as days
  from dual;

DAYS
----
 365