last day of the month parameter in ssrs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » last day of the month parameter in ssrs

21 posts, Page 1 of 3

1 2 3 »»»

last day of the month parameter in ssrs

Rate Topic

Display Mode

Topic Options

Author

Message

Bob Griffin

Posted Friday, December 12, 2008 8:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:57 AM
Points: 1,133, Visits: 667

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

Post #618643

Dave K-596875

Posted Monday, December 15, 2008 9:21 PM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 3:48 PM
Points: 684, Visits: 487

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.

Post #620145

dva2007

Posted Wednesday, December 17, 2008 4:56 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:05 AM
Points: 530, Visits: 1,020

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

Post #621082

jcrawf02

Posted Thursday, December 18, 2008 7:44 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, June 17, 2013 2:46 PM
Points: 2,561, Visits: 18,910

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
How to post performance problems
Tally Table:What it is and how it replaces a loop

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

Post #622085

Bob Griffin

Posted Thursday, December 18, 2008 10:56 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:57 AM
Points: 1,133, Visits: 667

dknaus,
I like your method better....

Thanks!

Post #622332

h_zulfi

Posted Wednesday, March 04, 2009 7:53 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:43 AM
Points: 11, Visits: 161

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

Post #668248

jgilbert 53295

Posted Thursday, December 03, 2009 10:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 03, 2009 12:56 PM
Points: 1, Visits: 1

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]

Post #828347

Abs-225476

Posted Friday, December 04, 2009 2:11 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:24 AM
Points: 206, Visits: 724

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.

Post #828691

ricky.chauvin

Posted Monday, January 04, 2010 7:28 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 07, 2011 6:29 AM
Points: 18, Visits: 43

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!

Post #841463

Lynn Pettis

Posted Monday, January 04, 2010 7:46 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 21,832, Visits: 27,862

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)

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #841473

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions