Date Parameter Help

Question

Post reply

Date Parameter Help

craig.bryant24

Valued Member

Points: 51
More actions
October 11, 2010 at 6:31 am

#230228

Hi,
I have created a report that has a begin date and end date parameter. I want to be able to fill this parameters automatically, so to do this I put an expression in the non-queried default values saying, =DateAdd("w",-2,today()) for both parameters. Because I want the reports to run only for 2 business days prior from today. This all works smooth, but my only problem is having it run 2 business days prior, so for example if I run the report today (monday) I want info from thursday on the report. With the current code I am getting info for Saturday. Any help is appreciated...Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 1

looks like you are subtracting 2 weeks from today's date, not 2 days. try dateadd("d", -2, today()).

steveb. SSC-Forever Points: 46733 More actions · Answer 2

I am not sure if there is a function that will deal correctly with business days as opposed to a calendar day. In this situation have you considered building date table to help with these sort of custom lookups?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Take a look at a function posted by Jeff Moden

http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

Using it with todays date and one day earlier:

SELECT Dbo.fn_WorkDays('10/11/2010','10/10/2010')

The function returned 1 as one working day.

Executing it as:

SELECT Dbo.fn_WorkDays('10/11/2010','10/08/2010')

The function returned two as working days.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

craig.bryant24 Valued Member Points: 51 More actions · Answer 4

thanks for the replies, I have not looked into creating a table yet. The code I am using right now is working smoothly, so I wasn't sure if there was some kind of iff statement I could use. Just trying to keep it as simple as possible. Any more help is greatly appreciated...Thanks again