Recipe9.5.Determining All Dates for a Particular Weekday Throughout a Year

Recipe 9.5. Determining All Dates for a Particular Weekday Throughout a Year

Problem

You want to find all the dates in a year that correspond to a given day of the week. For example, you may wish to generate a list of Fridays for the current year.

Solution

Regardless of vendor, the key to the solution is to return each day for the current year and keep only those dates corresponding to the day of the week that you care about. The solution examples retain all the Fridays.

DB2

Use the recursive WITH clause to return each day in the current year. Then use the function DAYNAME to keep only Fridays:

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

Oracle

Use the recursive CONNECT BY clause to return each day in the current year. Then use the function TO_CHAR to keep only Fridays:

 1   with x 2     as ( 3 select trunc(sysdate,'y')+level-1 dy 4   from t1 5   connect by level <= 6      add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') 7 ) 8 select * 9   from x    10  where to_char( dy, 'dy') = 'fri' 

PostgreSQL

Use the function GENERATE_SERIES to return each day in the current year. Then use the function TO_CHAR to keep only Fridays:

  1 select cast(date_trunc('year',current_date) as date)  2        + x.id as dy  3   from generate_series (  4         0,  5         ( select cast(  6                  cast(  7            date_trunc('year',current_date) as date)  8                       + interval '1 years' as date)  9                       - cast( 10                   date_trunc('year',current_date) as date) )-1 11         ) x(id) 12  where to_char( 13           cast( 14     date_trunc('year',current_date) 15                as date)+x.id,'dy') = 'fri' 

MySQL

Use the pivot table T500 to return each day in the current year. Then use the function DAYNAME to keep only Fridays:

  1 select dy  2   from (  3 select adddate(x.dy,interval t500.id-1 day) dy    4   from (  5 select dy, year(dy) yr  6   from (  7 select adddate(  8        adddate(current_date,  9                interval -dayofyear(current_date) day), 10                interval 1 day ) dy 11   from t1 12        ) tmp1 13        ) x, 14        t500 15  where year(adddate(x.dy,interval t500.id-1 day)) = x.yr 16        ) tmp2 17  where dayname(dy) = 'Friday' 

SQL Server

Use the recursive WITH clause to return each day in the current year. Then use the function DAYNAME to keep only Fridays:

  1   with x (dy,yr)  2     as (  3 select dy, year(dy) yr  4   from (  5 select getdate()-datepart(dy,getdate())+1 dy  6   from t1  7        ) tmp1  8  union all  9 select dateadd(dd,1,dy), yr 10   from x 11  where year(dateadd(dd,1,dy)) = yr 12 ) 13 select x.dy 14   from x 15  where datename(dw,x.dy) = 'Friday' 16 option (maxrecursion 400) 

Discussion

DB2

To find all the Fridays in the current year, you must be able to return every day in the current year. The first step is to find the first day of the year by using the DAYOFYEAR function. Subtract the value returned by DAYOFYEAR(CURRENT_DATE) from the current date to get December 31 of the prior year, and then add 1 to get the first day of the current year:

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

Now that you have the first day of the year, use the WITH clause to repeatedly add one day to the first day of the year until you are no longer in the current year. The result set will be every day in the current year (a portion of the rows returned by the recursive view X is shown below):

   with x (dy,yr)    as ( select dy, year(dy) yr   from ( select (current_date          dayofyear(current_date) days +1 days) as dy   from t1         ) tmp1 union all select dy+1 days, yr   from x  where year(dy +1 day) = yr ) select dy   from x DY ----------- 01-JAN-2005 … 15-FEB-2005 … 22-NOV-2005 … 31-DEC-2005 

The final step is to use the DAYNAME function to keep only rows that are Fridays.

Oracle

To find all the Fridays in the current year, you must be able to return every day in the current year. Begin by using the TRUNC function to find the first day of the year:

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

Next, use the CONNECT BY clause to return every day in the current year (to understand how to use CONNECT BY to generate rows, see "Generating Consecutive Time and Numeric Values" in Chapter 13).

As an aside, this recipe uses the WITH clause, but you can also use an inline view.

At the time of this writing, Oracle's WITH clause is not meant for recursive operations (unlike the case with DB2 and SQL Server); recursive operations are done using CONNECT BY. A portion of the result set returned by view X is shown below:

   with x    as ( select trunc(sysdate,'y')+level-1 dy from t1  connect by level <=     add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') ) select * from x DY ----------- 01-JAN-2005 … 15-FEB-2005 … 22-NOV-2005 … 31-DEC-2005 

The final step is to use the TO_CHAR function to keep only Fridays.

PostgreSQL

To find all the Fridays in the current year, you must be able to return a row for every day in the current year. To do that, use the GENERATE_SERIES function. The start and end values to be returned by GENERATE_SERIES are 0 and the number of days in the current year minus 1. The first parameter passed to GENERATE_SERIES is 0, while the second is a query that determines the number of days in the current year (because you are adding to the first day of the current year, you actually want to add 1 less than the number of days in the current year, so as to not spill over into the next year). The result returned by the second parameter of the GENERATE_SERIES function is shown below:

  select cast(        cast(  date_trunc('year',current_date) as date)             + interval '1 years' as date)             -cast(         date_trunc('year',current_date) as date)-1 as cnt   from t1 CNT --- 364 

Keeping in mind the result set above, the call to GENERATE_SERIES in the FROM clause will look like this: GENERATE_SERIES ( 0, 364 ). If you are in a leap year, such as 2004, the second parameter would be 365.

The next step after generating a list of dates in the year is to add the values returned by GENERATE_SERIES to the first day of the current year. A portion of the results is shown below:

  select cast(date_trunc('year',current_date) as date)        + x.id as dy   from generate_series (        0,        ( select cast(                 cast(           date_trunc('year',current_date) as date)                      + interval '1 years' as date)                      -cast(                  date_trunc('year',current_date) as date) )-1        ) x(id) DY ----------- 01-JAN-2005 … 15-FEB-2005 … 22-NOV-2005 … 31-DEC-2005 

The final step is to use the TO_CHAR function to keep only the Fridays.

MySQL

To find all the Fridays in the current year, you must be able to return every day in the current year. The first step is to find the first day of the year by using the DAYOF-YEAR function. Subtract the value returned by DAYOFYEAR(CURRENT_DATE) from the current date, and then add 1 to get the first day of the current year:

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

Then use table T500 to generate enough rows to return each day in the current year. You can do this by adding each value of T500.ID to the first day of the year until you break out of the current year. Partial results of this operation are shown below:

  select adddate(x.dy,interval t500.id-1 day) dy   from ( select dy, year(dy) yr   from ( select adddate(        adddate(current_date,                interval -dayofyear(current_date) day),                interval 1 day ) dy   from t1         ) tmp1         ) x,         t500  where year(adddate(x.dy,interval t500.id-1 day)) = x.yr DY ----------- 01-JAN-2005 … 15-FEB-2005 … 22-NOV-2005 … 31-DEC-2005 

The final step is to use the DAYNAME function to keep only Fridays.

SQL Server

To find all the Fridays in the current year, you must be able to return every day in the current year. The first step is to find the first day of the year by using the DATEPART function. Subtract the value returned by DATEPART(DY,GETDATE( )) from the current date, and then add 1 to get the first day of the current year:

  select getdate()-datepart(dy,getdate( ))+1 dy   from t1 DY ----------- 01-JAN-2005 

Now that you have the first day of the year, use the WITH clause and the DATEADD function to repeatedly add one day to the first day of the year until you are no longer in the current year. The result set will be every day in the current year (a portion of the rows returned by the recursive view X is shown below):

  with x (dy,yr)   as ( select dy, year(dy) yr   from ( select getdate()-datepart(dy,getdate( ))+1 dy   from t1        ) tmp1  union all select dateadd(dd,1,dy), yr   from x  where year(dateadd(dd,1,dy)) = yr ) select x.dy   from x option (maxrecursion 400) DY ----------- 01-JAN-2005 … 15-FEB-2005 … 22-NOV-2005 … 31-DEC-2005 

Finally, use the DATENAME function to keep only rows that are Fridays. For this solution to work, you must set MAXRECURSION to at least 366 (the filter on the year portion of the current year, in recursive view X, guarantees you will never generate more than 366 rows).