# Generate list of dates -- one date per month going back to the beginning of the year

• Hello,

I have a the following problem. I am given a particular date as an input (could be any date). Based on this provided date, I need to generate a list of 12 dates -- one date per month covering the entire current year. Here is an example to clarify:

DECLARE @Today smalldatetime

SET @Today = '05-31-2010'

SELECT DATEADD(month, -4, @Today)

UNION ALL

SELECT DATEADD(month, -3, @Today)

UNION ALL

SELECT DATEADD(month, -2, @Today)

UNION ALL

SELECT DATEADD(month, -1, @Today)

UNION ALL

SELECT DATEADD(month, 0, @Today)

UNION ALL

SELECT DATEADD(month, 1, @Today)

UNION ALL

SELECT DATEADD(month, 2, @Today)

UNION ALL

SELECT DATEADD(month, 3, @Today)

UNION ALL

SELECT DATEADD(month, 4, @Today)

UNION ALL

SELECT DATEADD(month, 5, @Today)

UNION ALL

SELECT DATEADD(month, 6, @Today)

UNION ALL

SELECT DATEADD(month, 7, @Today)

This script produces the output I need, but it's obviously hard-coded. I can't use it, since I don't know in advance what months of they year my initial date will be in. Also note that I used 05-31 on purpose to illustrate the fact that DATEADD function gracefully handles nonexistent dates. For example, it returns 02-28-2010 for February.

I hope my question is clear, but just in case, here is another example. Let's say my initial date is '12-30-2010'. In this case the desired output is produced by this code:

DECLARE @Today smalldatetime

SET @Today = '12-30-2010'

SELECT DATEADD(month, -11, @Today)

UNION ALL

SELECT DATEADD(month, -10, @Today)

UNION ALL

SELECT DATEADD(month, -9, @Today)

UNION ALL

SELECT DATEADD(month, -8, @Today)

UNION ALL

SELECT DATEADD(month, -7, @Today)

UNION ALL

SELECT DATEADD(month, -6, @Today)

UNION ALL

SELECT DATEADD(month, -5, @Today)

UNION ALL

SELECT DATEADD(month, -4, @Today)

UNION ALL

SELECT DATEADD(month, -3, @Today)

UNION ALL

SELECT DATEADD(month, -2, @Today)

UNION ALL

SELECT DATEADD(month, -1, @Today)

UNION ALL

SELECT DATEADD(month, 0, @Today)

So the question is: how can I make it dynamic, so it returns correct list of 12 dates, no matter what input date is supplied?

Thank you!

• This should do it for you. Remarks are in the code.

`DECLARE @date datetime,`

``` @month tinyint; SET @date = '20101230'; -- get the month of the selected date SET @month = month(@date); WITH CTE AS ( -- need sequential numbers from 1 to 12 SELECT TOP (12) N = row_number() OVER (ORDER BY (SELECT 0)) FROM sys.objects ) -- subtract the month of the specified date -- from the sequential number created above SELECT DateAdd(month, N-@month, @Date) ```

` FROM CTE;`

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• Wayne, THANK YOU!!! This is perfect and exactly what I was looking for. I am glad there are smart folks like you on this site!

• mishaluba (10/19/2010)

Wayne, THANK YOU!!! This is perfect and exactly what I was looking for. I am glad there are smart folks like you on this site!

Wayne is one heck of a smart guy, mishaluba.. Don't forget to read thro his articles on this site.. Those are awesome..

• ColdCoffee (10/20/2010)

mishaluba (10/19/2010)

Wayne, THANK YOU!!! This is perfect and exactly what I was looking for. I am glad there are smart folks like you on this site!

Wayne is one heck of a smart guy, mishaluba.. Don't forget to read thro his articles on this site.. Those are awesome..

:blush:

Thanks for this. I don't feel that I deserve this kind of praise - I'm still learning so much myself! There are some truly amazing folks here on this site, and I'm nowhere near them.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!