Post reply

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

  • October 19, 2010 at 6:13 pm

    #239988

    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!

  • October 19, 2010 at 8:37 pm

    #1239448

    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!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 19, 2010 at 10:57 pm

    #1239468

    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!

  • October 20, 2010 at 12:08 am

    #1239486

    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..

  • October 20, 2010 at 7:09 pm

    #1240014

    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!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply