last day of the month parameter in ssrs

Question

Post reply

last day of the month parameter in ssrs

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

rnunley Valued Member Points: 61 More actions · Answer 1

Is there a way I can tweek that expression to get the last day od the current month?

Thanks

Nevermind I got

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 2

Groleau+SQL (9/1/2010)
SELECT _________ FROM ______ AS Data WHERE DATEPART(month, Data.The_Date_Field)
= DATEPART(month, DATEADD(month, -1, GETDATE()));
(Say, when I screw up, how do I delete my post instead of editing it to say "oops"?)

This is not a good approach to working with dates - or, in fact any columns. Using a function on a column eliminates SQL's ability to consider an index on that column and will generally force a table scan.

The other issue you need to be aware of when working with datetime data types is the time component. Especially when using BETWEEN...

When you pass in the end date - with no time and then use BETWEEN this is what you are really getting:

WHERE somecolumn BETWEEN '20110101 00:00:00.000' AND '20110131 00:00:00.000'

Since '20110131 00:00:00.003' is not BETWEEN your date range it will not be included in the results. This will cause your report to always miss the last day - or the passed in date.

Now, the easiest way to avoid this is to use an open-interval range instead and modify your end date to the next day. As in:

WHERE somecolumn >= '20110101 00:00:00.000'

AND somecolumn < dateadd(day, 1, '20110131 00:00:00.000')

This will insure that you include everything up to the end of the day - and not include anything on the next day.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

asushil SSC Journeyman Points: 97 More actions · Answer 3

why not to assign

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

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

to default assigned values rather then calling sql select

//Ravi SSCrazy Points: 2612 More actions · Answer 4

I was looking at this thread, and then I figured out stuff myself. Here it is for SSRS expressions:

First day of last month: =DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

Last day of Last month: =DateAdd("s" ,-3,(DateSerial(Year(Now), Month(Now), 1)))

And yes, Last day of last month defaults to datetime format with time of 11:59:57

------------
🙂

gere_g SSC Journeyman Points: 79 More actions · Answer 5

=Format(DateSerial(Year(Parameters!ReportDate.Value), Month(Parameters!ReportDate.Value),1), "MM-dd-yyyy")

Lakshman Thilakaratne Grasshopper Points: 12 More actions · Answer 6

You can get last day of previous month in SSRS.

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

Lakshman

lkennedy76 SSChampion Points: 13683 More actions · Answer 7

I know this post is very old and I went round and round for days trying to figure out why my report was cutting off the last day of the month when I used the previous end of month date. I thought it could have been in my query where DateCol >= @StartReportDate and DateCol <= @EndReportDate so I changed it to where between @StartReportDate and @EndReportDate and still it would cut off the last day of the month. I finally got it when I added this into the default date for my report.

Happy Reporting 🙂

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

my date format is 2005-08-03 00:00:00.000

This reply was modified 11 months, 3 weeks ago by lkennedy76.
This reply was modified 11 months, 3 weeks ago by lkennedy76.
This reply was modified 11 months, 3 weeks ago by lkennedy76.

MCSE SQL Server 2012\2014\2016