Post reply

list of month names and week ranges between dates

  • February 2, 2009 at 7:08 pm

    #936866

    [font="Verdana"]Heh, that's kinda ironic. Jeff Moden asking for a URL to one of his own posts.[/font]

  • February 2, 2009 at 7:56 pm

    #936882

    Bruce W Cassidy (2/2/2009)

    [font="Verdana"]Heh, that's kinda ironic. Jeff Moden asking for a URL to one of his own posts.[/font]

    It had to happen, someday. It's funny as hell that's the way it worked out. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 2, 2009 at 7:58 pm

    #936883

    ghughes (2/2/2009)

    ok, you are right. At the time I was swamped and trying to fix the next dozen problems on my list. I cant find my link to it, but I did find this and will make it easy to modify and make it work. I spent the last hour looking thru my history so forgive me. 🙁

    http://qa.sqlservercentral.com/Forums/Topic494640-149-1.aspx

    Thank you for taking the time. Seriously... it could have been someone else's post.

    If you take a look at the code I posted above and replace "Number" with "N" and "spt_Values" with "Tally" and get rid of the criteria for TYPE, it'll work with the Tally table. Or not... it'll work just like it is in SQL Server 2000.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 3, 2009 at 4:38 am

    #937013

    ghughes (2/2/2009)

    ok, I give up. I defer to everyone greater judgement in the matter. Make perfect sense. I have created table......thank you for the help.

    in the 2005 answer, just search the SQL Centeral for 'list of months', that is how I found it

    BTW, if you created a Tally table, here's the same solution I posted above but using a Tally table...

    --===== Display the last 13 months as MonYYYY

    SELECT RIGHT(CONVERT(CHAR(11),DATEADD(mm,DATEDIFF(mm,0,GETDATE())-N,0),106),8) AS MonYYYY

    FROM dbo.Tally

    WHERE N BETWEEN 1 AND 13

    --===== Display the last 13 months as week date ranges

    SELECT CONVERT(CHAR(6),Week,106)+'-'+CONVERT(CHAR(6),Week+6,106)

    FROM (

    SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))-N,0) AS Week

    FROM dbo.Tally

    WHERE N BETWEEN 1 AND 66

    )d

    WHERE Week >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-13,0)

    Works in SQL Server 7, 2000, 2005, 2008...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 16 through 18 (of 18 total)

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