Select all days that are the last day of the month from a calendar table

Question

Post reply

Select all days that are the last day of the month from a calendar table

Chrissy321

SSCoach

Points: 15763
More actions
May 17, 2012 at 3:24 pm

#392283

HI All,
I want to select all days that are the last day of the month from a calendar table.
I guess I could say where
(MONTH(caldate) = 1 and DAY(caldate) = 31) OR
(MONTH(caldate) = 2 and DAY(caldate) = 28) OR
(MONTH(caldate) = 2 and DAY(caldate) = 29) OR
(MONTH(caldate) = 3 and DAY(caldate) = 31) OR
...etc..
How would you much wiser folks approach this?

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

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

Haining SSCrazy Points: 2118 More actions · Answer 1

How about,

declare @mydate datetime = '03/3/2010'

--Last day of the month

select DATEDIFF(DD, DATEADD(MM, 1, @mydate), @mydate) as 'Number of Days'

,DATEADD(MM, 1, @mydate) as 'A month Later'

,DATEADD(DD, -1, DATEADD(MM,DATEDIFF(MM,0,@mydate)+1,0)) as 'Last Day of Month'

,case

when @mydate = DATEADD(DD, -1, DATEADD(MM,DATEDIFF(MM,0,@mydate)+1,0))

then 'Last Day of Month'

else 'current day'

end;

Chrissy321 SSCoach Points: 15763 More actions · Answer 2

Sorry to answer my own question. I really did google this and I read the first 5 pages. On the sixth page I found

WHERE MyDate = DATEADD(dd, -DAY(DATEADD(m,1,MyDate)), DATEADD(m,1,MyDate))

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

Just a sidebar... when I make a calendar table, I generally have 2 date columns. "Today" and "Tomorrow". It not only helps more easily group data by day, but it also makes lookups like this much easier.

SELECT * FROM dbo.Calendar WHERE Day(Tomorrow) = 1.

Of course, if the project I'm working on has a whole lot of month end or monthly calculations, I just make a "month calendar" table to fit the task at hand. No one said you can't have more than one helper table.

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