In SQL Server Reporting Services, how would I get calculate the last day of the current month?
+2
A:
Here is the answer I came up with
=DateSerial(Year(Now()), Month(Now()), "1").AddMonths(1).AddDays(-1)
Cory
2010-05-17 21:20:29
@Cory Should mark this as the accepted answer then =).
ajdams
2010-05-17 21:21:14
Like buying a gun, there is a waiting period before you can accept your own answer. I'll mark it as accepted in 2 days.
Cory
2010-05-17 22:38:19
A:
you can use an assembly for doing this work by adding it as a reference.
masoud ramezani
2010-05-18 11:05:00
A:
From the blog of a Microsoft SQL Team member:
-- returns the last day of the current month.
select dbo.Date(year(getdate()), month(getdate())+1,0)
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
Hope this helps! --Dubs
Dubs
2010-05-18 17:42:34
Thanks but I was looking for how to do this not as a SQL query but as an SSRS expression
Cory
2010-05-18 21:40:04
+1
A:
I know you've found your own answer, but I'd suggest this alternative:
=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)
It's a little easier to read, in my opinion, and might have slightly better performance (though most likely unnoticeable)
And, of course, if you wanted to pad out that date to 23:59:59, as is often necessary, just modify slightly:
=Today.AddDays(1-Today.Day).AddMonths(1).AddSeconds(-1)
JC
2010-05-23 02:23:57