Post reply

last day of the month parameter in ssrs

  • December 12, 2008 at 8:35 am

    #202886

    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

  • December 15, 2008 at 9:21 pm

    #912916

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

    selectdateadd(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.

  • December 17, 2008 at 4:56 am

    #913708

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

  • December 18, 2008 at 7:44 am

    #914546

    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."

  • December 18, 2008 at 10:56 am

    #914760

    dknaus,

    I like your method better....

    Thanks!

  • March 4, 2009 at 7:53 am

    #953212

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

  • December 3, 2009 at 10:12 am

    #1087246

    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]

  • December 4, 2009 at 2:11 am

    #1087534

    Dave K-596875 (12/15/2008)

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

    selectdateadd(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.

  • January 4, 2010 at 7:28 am

    #1098413

    I tend to use these:

    http://www.databasejournal.com/features/mssql/article.php/3076421/Examples-of-how-to-Calculate-Different-SQL-Server-Dates.htm

    Very useful!

  • January 4, 2010 at 7:46 am

    #1098420

    ricky.chauvin (1/4/2010)

    I tend to use these:

    http://www.databasejournal.com/features/mssql/article.php/3076421/Examples-of-how-to-Calculate-Different-SQL-Server-Dates.htm

    Very useful!

    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)

  • January 4, 2010 at 8:28 am

    #1098458

    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 🙂

  • January 4, 2010 at 8:29 am

    #1098460

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

  • April 28, 2010 at 11:46 am

    #1159178

    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

  • September 1, 2010 at 9:05 am

    #1215064

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

  • September 1, 2010 at 9:14 am

    #1215077

    oops

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

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