Recipe9.2.Determining the Number of Days in a Year


Recipe 9.2. Determining the Number of Days in a Year

Problem

You want to count the number of days in the current year.

Solution

The number of days in the current year is the difference between the first day of the next year and the first day of the current year (in days). For each solution the steps are:

  1. Find the first day of the current year.

  2. Add one year to that date (to get the first day of the next year).

  3. Subtract the current year from the result of Step 2.

The solutions differ only in the built-in functions that you use to perform these steps.

DB2

Use the function DAYOFYEAR to help find the first day of the current year, and use DAYS to find the number of days in the current year:

 1 select days((curr_year + 1 year)) - days(curr_year) 2   from ( 3 select (current_date - 4         dayofyear(current_date) day + 5          1 day) curr_year 6   from t1 7        ) x 

Oracle

Use the function TRUNC to find the beginning of the current year, and use ADD_ MONTHS to then find the beginning of next year:

 1 select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') 2   from dual 

PostgreSQL

Use the function DATE_TRUNC to find the beginning of the current year. Then use interval arithmetic to determine the beginning of next year:

 1 select cast((curr_year + interval '1 year') as date) - curr_year 2   from ( 3 select cast(date_trunc('year',current_date) as date) as curr_year 4   from t1 5        ) x 

MySQL

Use ADDDATE to help find the beginning of the current year. Use DATEDIFF and interval arithmetic to determine the number of days in the year:

 1 select datediff((curr_year + interval 1 year),curr_year) 2   from ( 3 select adddate(current_date,-dayofyear(current_date)+1) curr_year 4   from t1 5        ) x 

SQL Server

Use the function DATEADD to find the first day of the current year. Use DATEDIFF to return the number of days in the current year:

 1 select datediff(d,curr_year,dateadd(yy,1,curr_year)) 2   from ( 3 select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year 4   from t1 5        ) x 

Discussion

DB2

The first step is to find the first day of the current year. Use DAYOFYEAR to determine how many days you are into the current year. Subtract that value from the current date to get the last day of last year, and then add 1:

  select (current_date         dayofyear(current_date) day +          1 day) curr_year   from t1 CURR_YEAR ----------- 01-JAN-2005 

Now that you have the first day of the current year, just add one year to it; this gives you the first day of next year. Then subtract the beginning of the current year from the beginning of next year.

Oracle

The first step is to find the first day of the current year, which you can easily do by invoking the built-in TRUNC function and passing 'Y' as the second argument (thereby truncating the date to the beginning of the year):

  select select trunc(sysdate,'y') curr_year   from dual CURR_YEAR ----------- 01-JAN-2005 

Then add one year to arrive at the first day of the next year. Finally, subtract the two dates to find the number of days in the current year.

PostgreSQL

Begin by finding the first day of the current year. To do that, invoke the DATE_ TRUNC function as follows:

  select cast(date_trunc('year',current_date) as date) as curr_year   from t1 CURR_YEAR ----------- 01-JAN-2005 

You can then easily add a year to compute the first day of next year. Then all you need to do is to subtract the two dates. Be sure to subtract the earlier date from the later date. The result will be the number of days in the current year.

MySQL

Your first step is to find the first day of the current year. Use DAYOFYEAR to find how many days you are into the current year. Subtract that value from the current date, and add 1:

  select adddate(current_date,-dayofyear(current_date)+1) curr_year   from t1 CURR_YEAR ----------- 01-JAN-2005 

Now that you have the first day of the current year, your next step is to add one year to it to get the first day of next year. Then subtract the beginning of the current year from the beginning of the next year. The result is the number of days in the current year.

SQL Server

Your first step is to find the first day of the current year. Use DATEADD and DATEPART to subtract from the current date the number of days into the year the current date is, and add 1:

  select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year   from t1 CURR_YEAR ----------- 01-JAN-2005 

Now that you have the first day of the current year, your next step is to add one year to it get the first day of the next year. Then subtract the beginning of the current year from the beginning of the next year. The result is the number of days in the current year.




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net