Common Date Calculations for Parameters and SQL variables

I’m commonly asked to make the parameters for the date start with specific months. I’m typically asked for its First Day Last Month or Last Day Previous Month. Here are the T-SQL and SSRS Expressions to create the dates:

Using T-SQL:

--First Day of Current Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

--Last Day of Current Month

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

 --First Day of Previous Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

--Last Day of Previous Month

SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

 

When you need to create these directly in the SSRS Report. Use the following:

 

--First date of Current Month

=Today.AddDays(1-Today.Day)

 --last date of Current Month

=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)

 --First date of Current Year

=Today.AddMonths(1-Today.month).AddDays(1-Today.day)

 --Last date of Current Year

=Today.AddDays(1-Today.Day).AddMonths(13-today.month).AddDays(-1)

 --Previous Month Last Date

=DateAdd("d",-(Day(today)), Today)

--Previous Month First Date

=dateadd(dateinterval.month, -1, today().AddDays(-(today().Day-1)))