How do I get the last day on the month using SQL Reporting Services

Question

In SQL Server Reporting Services, how would I calculate the last day of the current month?

Quentinquercetin

Answer 1

Here is the answer I came up with

=DateSerial(Year(Now()), Month(Now()), "1").AddMonths(1).AddDays(-1)

Quentinquercetin

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. – Quentinquercetin

i like this approach! – Rendering

Answer 2

As it took me a while to Figure this out, and JC's answer was the simplest to modify and had a good logical structure. I expanded it Slightly for others searching for answers on this topic. I have found normally you don't just want the Last Day of a month / year you also want the first day of the month / year. So here they are the full lines to calculate just that. wtih the SQl version there also.

First Day of Current month

SSRS=Today.AddDays(1-Today.Day)
SQL=SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

Last day of Current Month

SSRS=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)
SQL=SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

First Day of Current year

SSRS=Today.AddMonths(1-Today.month).AddDays(1-Today.day)
SQL=SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

Last Day of Current Year

SSRS=Today.AddDays(1-Today.Day).AddMonths(13-today.month).AddDays(-1)
SQL=SELECT DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)))

I hope this helps somone.

Alrzc

Absolute best ever overview on what to do. Thanks @Alrzc – Torrey

Answer 3

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)

Schwaben

Answer 4

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

Devoice

Thanks but I was looking for how to do this not as a SQL query but as an SSRS expression – Quentinquercetin

Answer 5

You may try this expression, Just replace now with your date field.

=DateSerial(Year(Now), Month(Now), 1)

Hope this helps.

Regards

Mechanist

Answer 6

There is an even easier way as in the marked answer:

=DateSerial(Year(Now()), Month(Now())+1, 0)

Since DateSerial() accepts integers as parameters one does not have to use AddMonths() and AddDays(). As in the example an instant calculation inside DateSerial() is possible.

Furthermore day 1 is the first day of the month while the first day minus one day is the last day of the month before (1-1=0). So the example will return the date of the last day of the current month.

Loafer

Answer 7

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

Theological

Perhaps a little more explanation would be inorder. – Oxidimetry