CODEWITHSUNDEEP
reporting-services
Jonathan Holston
Jonathan Holston

Reputation: 331

First and Last day of current month

In SQL Server Reporting Services; How can I calculate the first and last day of the previous month?

I know I can use the expression below to get the last day of the current month, but I'm stuck when trying to find the first and last of the previous month.

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

Upvotes: 33

Views: 97442

Answers (9)

justine ramos
justine ramos

Reputation: 171

First day of this month: =dateadd("m",0,dateserial(year(Today),month(Today),1))

Last day of this month: =dateadd("m",1,dateserial(year(Today),month(Today),0))

First day of last month: =dateadd("m",-1,dateserial(year(Today),month(Today),1))

First day of next month: =dateadd("m",1,dateserial(year(Today),month(Today),1))

Last day of last month: =dateadd("m",0,dateserial(year(Today),month(Today),0))

Last day of next month: =dateadd("m",2,dateserial(year(Today),month(Today),0))

Upvotes: 16

Ankur Shah
Ankur Shah

Reputation: 507

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

Upvotes: 0

I-ILI
I-ILI

Reputation: 23

=DateSerial(Year(Now), Month(Now), 1) for first day of the month

and

=DateSerial(Year(Now), Month(Now)+1, 0) for the last day of the month.

http://www.answermysearches.com/ssrs-how-to-set-a-default-parameter-to-the-first-day-of-the-month/2167/

Upvotes: 2

Chris
Chris

Reputation: 11

I was able to accomplish this, and show it in mm/dd format by using:

=Format(DateAdd("D", -1, (DateAdd("M", 1, yourdate.value))), "MM/dd")

Upvotes: 1

vijayakumar
vijayakumar

Reputation: 99

For current month

 =DateSerial(Year(Parameters!ParameterName.Value), Month(Parameters!ParameterName.Value), "1").AddMonths(1).AddDays(-1)

Previous month last day:

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

Previous month first day:

=DateSerial(Year(Parameters!ParameterName.Value), Month(Parameters!ParameterName.Value), "1").AddMonths(-1)

Upvotes: 10

Andrew Casal
Andrew Casal

Reputation: 91

Using Native VB Functions

First Day of Previous Month

=DateAdd("m", -1, DateSerial(Year(Today()), Month(Today()), 1))

Last Day of Previous Month

=DateAdd("d", -1, DateSerial(Year(Today()), Month(Today()), 1))

Upvotes: 1

lilalinux
lilalinux

Reputation: 3031

First of previous month:

=DateAdd("m",-1,DateAdd("d",1-(Day(Today)), Today))

Upvotes: 2

Måns H
Måns H

Reputation: 21

Previous month last date:

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

Upvotes: 2

Austin Salonen
Austin Salonen

Reputation: 50215

Just a guess based on your code working.

--previous month last
=DateSerial(Year(Now()), Month(Now()), "1").AddDays(-1)

--previous month first
=DateSerial(Year(Now()), Month(Now()), "1").AddMonths(-1)

Upvotes: 42

Related Questions