Recipe9.1.Determining if a Year Is a Leap Year


Recipe 9.1. Determining if a Year Is a Leap Year

Problem

You want to determine whether or not the current year is a leap year.

Solution

If you've worked on SQL for some time, there's no doubt that you've come across several techniques for solving this problem. Just about all the solutions I've encountered work well, but the one presented in this recipe is probably the simplest. This solution simply checks the last day of February; if it is the 29th then the current year is a leap year.

DB2

Use the recursive WITH clause to return each day in February. Use the aggregate function MAX to determine the last day in February.

  1   with x (dy,mth)  2     as (  3 select dy, month(dy)  4   from (  5 select (current_date -  6          dayofyear(current_date) days +1 days)  7           +1 months as dy  8   from t1  9        ) tmp1 10  union all 11 select dy+1 days, mth 12   from x 13  where month(dy+1 day) = mth 14 ) 15 select max(day(dy)) 16   from x 

Oracle

Use the function LAST_DAY to find the last day in February:

 1 select to_char( 2          last_day(add_months(trunc(sysdate,'y'),1)), 3         'DD') 4   from t1 

PostgreSQL

Use the function GENERATE_SERIES to return each day in February, then use the aggregate function MAX to find the last day in February:

  1 select max(to_char(tmp2.dy+x.id,'DD')) as dy  2   from (  3 select dy, to_char(dy,'MM') as mth  4   from (  5 select cast(cast(  6             date_trunc('year',current_date) as date)  7                        + interval '1 month' as date) as dy  8   from t1  9        ) tmp1 10        ) tmp2, generate_series (0,29) x(id) 11  where to_char(tmp2.dy+x.id,'MM') = tmp2.mth 

MySQL

Use the function LAST_DAY to find the last day in February:

 1 select day( 2        last_day( 3        date_add( 4        date_add( 5        date_add(current_date, 6                 interval -dayofyear(current_date) day), 7                 interval 1 day), 8                 interval 1 month))) dy 9   from t1 

SQL Server

Use the recursive WITH clause to return each day in February. Use the aggregate function MAX to determine the last day in February:

  1   with x (dy,mth)  2     as (  3 select dy, month(dy)  4   from (  5 select dateadd(mm,1,(getdate( )-datepart(dy,getdate( )))+1) dy  6   from t1  7        ) tmp1  8  union all  9 select dateadd(dd,1,dy), mth 10   from x 11  where month(dateadd(dd,1,dy)) = mth 12 ) 13 select max(day(dy)) 14   from x 

Discussion

DB2

The inline view TMP1 in the recursive view X returns the first day in February by:

  1. Starting with the current date

  2. Using DAYOFYEAR to determine the number of days into the current year that the current date represents

  3. Subtracting that number of days from the current date to get December 31 of the prior year, and then adding one to get to January 1 of the current year

  4. Adding one month to get to February 1

The result of all this math is shown below:

   select (current_date            dayofyear(current_date) days +1 days) +1 months as dy    from t1 DY ----------- 01-FEB-2005 

The next step is to return the month of the date returned by inline view TMP1 by using the MONTH function:

  select dy, month(dy) as mth   from ( select (current_date           dayofyear(current_date) days +1 days) +1 months as dy   from t1        ) tmp1 DY          MTH ----------- --- 01-FEB-2005   2 

The results presented thus far provide the start point for the recursive operation that generates each day in February. To return each day in February, repeatedly add one day to DY until you are no longer in the month of February. A portion of the results of the WITH operation is shown below:

    with x (dy,mth)     as ( select dy, month(dy)   from ( select (current_date -          dayofyear(current_date) days +1 days) +1 months as dy   from t1        ) tmp1  union all  select dy+1 days, mth    from x   where month(dy+1 day) = mth  )  select dy,mth    from x DY          MTH ----------- --- 01-FEB-2005   2 … 10-FEB-2005   2 … 28-FEB-2005   2 

The final step is to use the MAX function on the DY column to return the last day in February; if it is the 29th, you are in a leap year.

Oracle

The first step is to find the beginning of the year using the TRUNC function:

  select trunc(sysdate,'y')   from t1 DY ----------- 01-JAN-2005 

Because the first day of the year is January 1st, the next step is to add one month to get to February 1st:

  select add_months(trunc(sysdate,'y'),1) dy   from t1 DY ----------- 01-FEB-2005 

The next step is to use the LAST_DAY function to find the last day in February:

  select last_day(add_months(trunc(sysdate,'y'),1)) dy   from t1 DY ----------- 28-FEB-2005 

The final step (which is optional) is to use TO_CHAR to return either 28 or 29.

PostgreSQL

The first step is to examine the results returned by inline view TMP1. Use the DATE_TRUNC function to find the beginning of the current year and cast that result as a DATE:

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

The next step is to add one month to the first day of the current year to get the first day in February, casting the result as a date:

  select cast(cast(             date_trunc('year',current_date) as date)                        + interval '1 month' as date) as dy   from t1 DY ----------- 01-FEB-2005 

Next, return DY from inline view TMP1 along with the numeric month of DY. Return the numeric month by using the TO_CHAR function:

  select dy, to_char(dy,'MM') as mth    from (  select cast(cast(              date_trunc('year',current_date) as date)                         + interval '1 month' as date) as dy    from t1         ) tmp1 DY          MTH ----------- --- 01-FEB-2005   2 

The results shown thus far comprise the result set of inline view TMP2. Your next step is to use the extremely useful function GENERATE_SERIES to return 29 rows (values 1 through 29). Every row returned by GENERATE_SERIES (aliased X) is added to DY from inline view TMP2. Partial results are shown below:

  select tmp2.dy+x.id as dy, tmp2.mth   from ( select dy, to_char(dy,'MM') as mth   from ( select cast(cast(             date_trunc('year',current_date) as date)                        + interval '1 month' as date) as dy   from t1        ) tmp1        ) tmp2, generate_series (0,29) x(id)  where to_char(tmp2.dy+x.id,'MM') = tmp2.mth DY          MTH ----------- --- 01-FEB-2005  02 … 10-FEB-2005  02 … 28-FEB-2005  02 

The final step is to use the MAX function to return the last day in February. The function TO_CHAR is applied to that value and will return either 28 or 29.

MySQL

The first step is to find the first day of the current year by subtracting from the current date the number of days it is into the year, and then adding one day. Do all of this with the DATE_ADD function:

  select date_add(        date_add(current_date,                 interval -dayofyear(current_date) day),                 interval 1 day) dy   from t1 DY ----------- 01-JAN-2005 

Then add one month again using the DATE_ADD function:

  select date_add(        date_add(        date_add(current_date,                 interval -dayofyear(current_date) day),                 interval 1 day),                 interval 1 month) dy   from t1 DY ----------- 01-FEB-2005 

Now that you've made it to February, use the LAST_DAY function to find the last day of the month:

  select last_day(        date_add(        date_add(        date_add(current_date,                 interval -dayofyear(current_date) day),                 interval 1 day),                 interval 1 month)) dy   from t1 DY ----------- 28-FEB-2005 

The final step (which is optional) is to use the DAY function to return either a 28 or 29.

SQL Server

This solution uses the recursive WITH clause to generate each day in February. The first step is to find the first day of February. To do this, find the first day of the current year by subtracting from the current date the number of days it is into the year, and then adding one day. Once you have the first day of the current year, use the DATEADD function to add one month to advance to the first day of February:

  select dateadd(mm,1,(getdate()-datepart(dy,getdate()))+1) dy   from t1 DY ----------- 01-FEB-2005 

Next, return the first day of February along with the numeric month for February:

  select dy, month(dy) mth   from ( select dateadd(mm,1,(getdate()-datepart(dy,getdate()))+1) dy   from t1        ) tmp1 DY          MTH ----------- --- 01-FEB-2005   2 

Then use the recursive capabilities of the WITH clause to repeatedly add one day to DY from inline view TMP1 until you are no longer in February (partial results shown below):

     with x (dy,mth)     as ( select dy, month(dy)   from ( select dateadd(mm,1,(getdate()-datepart(dy,getdate()))+1) dy   from t1        ) tmp1  union all select dateadd(dd,1,dy), mth   from x  where month(dateadd(dd,1,dy)) = mth  ) select dy,mth from x DY          MTH ----------- --- 01-FEB-2005  02 … 10-FEB-2005  02 … 28-FEB-2005  02 

Now that you can return each day in February, the final step is to use the MAX function to see if the last day is the 28th or 29th. As an optional last step, you can use the DAY function to return a 28 or 29, rather than a date.




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