Getting all the alternate Mondays in the Year
Posted by decipherinfosys on December 30, 2008
Got an interesting question from one of our readers yesterday. They have project plans where they do releases on every other Monday – he wanted to know if there is a quick way in T-SQL to generate a list of all bi-weekly Mondays in a year. He was referencing one of our earlier posts in which we had discussed how to generate dates between two date values and wanted to know if we can extend it to provide him an answer to this current question as well. It can definitely be done by making changes to that UDF but there is an even simpler way to achieve this.
We will make use of the CTE solution that we had discussed before in a couple of our posts in order to generate a number table. Using the same solution, we can get the desired output by using this SQL:
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n%10) – 1 FROM N4 AS X, N4 AS Y)
select DATEADD(dd, n * 14, ‘20090105’) as Monday_Date
WHERE DATEADD(dd, n * 14, ‘20090105’) <= ‘20091231’
Or, if you use a number table in your environment then the above query could be based directly at the top of it. That is all what the above CTE does – prepare a number table based off which we can write of the query to get the desired result.
And in the above query, you can easily substitute the values for the dates as parameters and make it generic for any year.