last day of the month parameter in ssrs

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

    Thanks

    Nevermind I got

  • 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

  • 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

  • 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

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

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

  • You can get last day of previous month in SSRS.

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

    Lakshman

  • 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 3 months, 2 weeks ago by  lkennedy76.
    • This reply was modified 3 months, 2 weeks ago by  lkennedy76.
    • This reply was modified 3 months, 2 weeks ago by  lkennedy76.

    MCSE SQL Server 2012\2014\2016

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

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