list of month names and week ranges between dates

Question

list of month names and week ranges between dates

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

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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://www.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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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)