Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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

like image 594
Cory Avatar asked May 17 '10 21:05

Cory


2 Answers

Here is the answer I came up with

=DateSerial(Year(Now()), Month(Now()), "1").AddMonths(1).AddDays(-1)
like image 134
Cory Avatar answered Sep 21 '22 08:09

Cory


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.

like image 38
Technane Avatar answered Sep 18 '22 08:09

Technane