February 12, 2009 at 2:57 pm
I have inherited a colleague's code & I recognize the test for day one of the month, but can someone verify this is checking to see if this is the last day of the month?
if DAY(DATEADD(d, -DAY(DATEADD(m,1,getdate())),DATEADD(m,1,getdate()))) = day(getdate())
If I run SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,getdate())),DATEADD(m,1,getdate()))) = day(getdate())
The result is 28.....but I just want to make sure.
Thanx!
Jude
February 12, 2009 at 3:06 pm
Yes, that should get the date of the last day of the current month. Test it with a few dates like Jan 31, in place of getdate, and see what it does in those cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 3:21 pm
Here's another way of calculating the first and last days of the current month
SELECT FirstOfMonth = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),
LastOfMonth = DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, -1)
February 12, 2009 at 5:25 pm
[font="Verdana"]Just a little plug for using a Calendar table. Then you can write code similar to the following:
select CalendarID, CalendarDate
from dbo.Calendar
where YearNumber = year(getdate()) and
MonthNumber = month(getdate()) and
IsEndOfMonth = 1;
[/font]
February 12, 2009 at 10:56 pm
andrewd.smith (2/12/2009)
SELECT FirstOfMonth = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),LastOfMonth = DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, -1)
A nice and simple way 🙂
Here's another way I used to use:
select dateadd(day,-1,dateadd(month,1,str(year(getdate()),4)+'-'+str(month(getdate()),2)+'-01'))
February 13, 2009 at 8:01 am
Thanx to you all!!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply