last day of the month parameter in ssrs

  • Bob Griffin

    SSCertifiable

    Points: 5982

    I occasionally need to schedule reports that run for an entire month and automatically email them. A monthly report, of course, must have the correct ending date, either 28, 30, 31 (and don't forget leap year). Since these type reports typically run within the first week of the new month I needed a parameter that would always pull the proper last day of the previous month

    After much fighting with the expression builder in ssrs 2005, I finally found a way to do this and I wanted to share it with the community and also get your feedback....Here's the expression

    =dateadd("d",-1,CDate(cstr(day(today.now())) + "/1/" + cstr(year(today.now()))))

    It simply takes the first day of this month and subtracts one day from it giving the last day of the previous month...

    If you have a better way to accomplish this task, I'd love to see it :w00t:

    Thanks,

    Robert

  • Dave K-596875

    Ten Centuries

    Points: 1006

    Assuming you have a SQL data source, you can create a dataset using this query:

    select dateadd(mm,datediff(mm,0,getdate())-1,0) [FirstDate],

    dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate()),0)) [LastDate]

    It will return the first and last day of the previous month. Tie your parameters to the results of this query and you're set.

  • dva2007

    SSCertifiable

    Points: 7705

    thanks for sharing this guys...i was using TSQL and then using that into expression....

  • jcrawf02

    SSC-Insane

    Points: 24198

    If you need this type of info a lot, you could also consider a dates table. Recent article about building one.

    http://www.sqlservercentral.com/articles/Date+Manipulation/65195/

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Bob Griffin

    SSCertifiable

    Points: 5982

    dknaus,

    I like your method better....

    Thanks!

  • h_zulfi

    Mr or Mrs. 500

    Points: 507

    I have used the above expression but it gives error some times if the date is in American format.

    So am using now dateadd("d",-1,dateserial(year(Today),month(Today),1))

  • jgilbert 53295999

    SSC Rookie

    Points: 49

    Be careful with that query - if you're working with pure dates that may be fine, but if you're using datetime, you're losing the last day of the last month. It returns, for example, '2009-11-30 12:00:00 AM', but what you'd really want is '2009-11-30 23:59:99'.

    You could compensate for this a few different ways. What I did was simply add the number of seconds in a day, minus 1 (86399 seconds):

    select dateadd(mm,datediff(mm,0,getdate())-1,0) [FirstDate],

    dateadd(s, 86399, dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate()),0))) [LastDate]

  • Abs-225476

    Hall of Fame

    Points: 3483

    Dave K-596875 (12/15/2008)


    Assuming you have a SQL data source, you can create a dataset using this query:

    select dateadd(mm,datediff(mm,0,getdate())-1,0) [FirstDate],

    dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate()),0)) [LastDate]

    It will return the first and last day of the previous month. Tie your parameters to the results of this query and you're set.

    How do you change the format of these dates in an expression in reporting services?

    I want the format to be in dd/ monthname/ YYYY in an expression so that I can concatenate it with a string.

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Only issue I have with the subtracting of 3 milliseconds, it may work with the current datetime data type, but won't with the new datetime data types in SQL Server 2008.

    Also, when selecting from a date range, it would be better to use the following:

    WHERE

    MyDateTime >= BeginDateRange -- (ie 2009-12-01)

    AND MyDateTime < EndDateRange -- (ie 2010-01-01)

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    Touchwood i havnt had any problems running against 2008 yet (using ssrs2008).

    When im working between two dates in SSRS I tend to use:

    Between @startdate AND @enddate

    which has worked a treat so far 🙂

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    PS. Im shocked im able to contribute now 😀 go me!

  • JR_**C

    SSC Rookie

    Points: 41

    Hi,

    You would love me for this....

    Enter the expression in Report Parameters, Default,

    Non-Queried field:

    =DateSerial(Year(today()),Month(today())-1,1)

    This gives me the first day of the previous month. Take out the -1 to get

    the first day of the current month.

    To get the last day of the previous month, use:

    =DateSerial(Year(today()),Month(today()),0)

    This would only work with Expression builder though, I also had a hard time trying to figure out a way to get the first and last day of the month. This will work every time.

    Johana

  • Groleau+SQL

    SSC Enthusiast

    Points: 130

    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"?)

  • Groleau+SQL

    SSC Enthusiast

    Points: 130

    oops

Viewing 15 posts - 1 through 15 (of 21 total)

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