Home Forums SQL Server 2005 Business Intelligence Last Day of Month subscription for Shared SSRS schedule RE: Last Day of Month subscription for Shared SSRS schedule

  • August 14, 2012 at 11:07 am

    #1525676

    David92595 (8/14/2012)

    I created it as a stored procedure in SSMS so I could use it for multiple reports and then just add it as a dataset in SSRS.

    First Day of Previous Month:

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

    Last day of Previous Month (Time set to 11:59:59pm)

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    I don't remember what i did after that to get it working, but I think it's pretty obvious if you use the wizard. If you have any problems with this let me know and I'll look into it further.

    David92595

    What is the data type of the date/time values you compare the above to? A better way is to use an open-ended value at the upper end of the range test. Instead of

    somecolumn <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    you should use

    somecolumn < DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)