views:

186

answers:

4

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
@Cory Should mark this as the accepted answer then =).
ajdams
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
A: 

you can use an assembly for doing this work by adding it as a reference.

masoud ramezani
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
Thanks but I was looking for how to do this not as a SQL query but as an SSRS expression
Cory
+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