Query to get all days of a specific month and year

Question

Post reply

Query to get all days of a specific month and year

giontech

Ten Centuries

Points: 1094
More actions
April 28, 2008 at 10:09 pm

#380240

Hi
I've been trying to get a query to get all the days of the month passing in a month and year variable but just don't have a clue on how to do this.
I'm hoping a kind person from here can help me with this.
Thanks 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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

w.lengenfelder SSC Eights! Points: 884 More actions · Answer 1

It depends on how you want to access to this functionality.

the base code can be like

use AdventureWorks

go

DECLARE @pYear INT, @pMonth INT

DECLARE @pDateWork SMALLDATETIME, @pDateControl SMALLDATETIME

SET @pYear = 2008

SET @pMonth = 2

SET @pDateWork = CONVERT(SMALLDATETIME, CONVERT(VARCHAR,@pYear) + '-' +

CONVERT(VARCHAR,@pMonth) + '-1', 120)

SET @pDateControl = DATEADD(mm,1, @pDateWork)

WHILE @pDateWork < @pDateControl BEGIN

PRINT @pDateWork

SET @pDateWork = DATEADD(dd,1,@pDateWork)

END

You can access this by an userdefined Function or Stored Procedure

w. lengenfelder

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

For a single result set that you can join to...

--===== Declare your parameters

DECLARE @pYear INT, @pMonth INT

--===== Set the parameters (simulates a proc or udf parameters)

SELECT @pYear = 2008,

@pMonth = 2

SELECT t.N-1+DATEADD(mm,@pMonth-1,DATEADD(yy,@pYear-1900,0)) --Tally count-1 + 1st of desired month

FROM dbo.Tally t

WHERE t.N-1+DATEADD(mm,@pMonth-1,DATEADD(yy,@pYear-1900,0))

< DATEADD(mm,@pMonth,DATEADD(yy,@pYear-1900,0)) --First of next month

If you don't already have a Tally Table, now would be a good time. See the following for how to make one...

http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/

--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: 1004340 More actions · Answer 3

So... did that work for you or what?

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

Ian Crandell SSCommitted Points: 1603 More actions · Answer 4

Jeff,

I have seen you use a tally table way too many times (this is not a bad thing ;)). When I saw the title to this post, even before seeing the thread, I thought "Hmmm, couldn't you use a tally table for this?" (I figured I would see you posting if someone hadn't mentioned it, would have been surprised if you didn't :ermm: )

Ian.

"If you are going through hell, keep going."
-- Winston Churchill

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Ian Crandell (5/8/2008)
Jeff,
I have seen you use a tally table way too many times (this is not a bad thing ;)). When I saw the title to this post, even before seeing the thread, I thought "Hmmm, couldn't you use a tally table for this?" (I figured I would see you posting if someone hadn't mentioned it, would have been surprised if you didn't :ermm: )

Yeah... it had the word "Tally" written all over it. Couldn't resist it. 😀

Sure wish the OP would let me know if that did it for him.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/[/url]

N 56°04'39.16"
E 12°55'05.25"