date range parameter

i have a need by date in my report. i would like to create a date parameter
where the user can select today, 1 week, 1 month, 60, 90, etc. days between
current date and need by date. i've created parameters before, but this
one's giving me trouble. any ideas?Hi,
I think I would create 2 parameters like to work with that kind of
requirements
- Datevalue: would hold the value (Number)
- DateType: would hold the parameter type (DropDown) filled with value
like (Days,Weeks,Months,...)
After that i would do the calculation at the stored procedure level
using the T-SQL DATEADD function..
HTH,
Eric|||Create parameters using non-queried and put in the following (as example)
Label Value
Today 0
1 Week 7
30 Days 30
60 Days 60
90 Days 90
Then in your query do this:
select * from whatever where mydatefield < dateadd(dd,?, getdate())
Anyway, gives you an idea of how to do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> i have a need by date in my report. i would like to create a date
parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||In your where clause, you could have a statement such as:
WHERE
MyDate >= DATEADD(DAY, -@.DaysBack, GETDATE())
Then, in your parameter dropdown, you'll specify the number of days that
correlate to each value in the dropdown, e.g. 1 week - 7, etc.
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
>i have a need by date in my report. i would like to create a date
>parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||Thanks for your help, but when I tried to do this I got an error message,
"argument data type datetime is invalid for argument 2 of dateadd function"
My where statement reads:
WHERE (mydate <= DATEDIFF(dd, @.date , GETDATE()))
@.date being the name of my parameter.
"Bruce L-C [MVP]" wrote:
> Create parameters using non-queried and put in the following (as example)
> Label Value
> Today 0
> 1 Week 7
> 30 Days 30
> 60 Days 60
> 90 Days 90
> Then in your query do this:
> select * from whatever where mydatefield < dateadd(dd,?, getdate())
> Anyway, gives you an idea of how to do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> > i have a need by date in my report. i would like to create a date
> parameter
> > where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> > current date and need by date. i've created parameters before, but this
> > one's giving me trouble. any ideas?
>
>|||Hi,
After giving it a little more tought, Here is what i would do:
Create 2 parameters
- DateValue (Integer) holding the value
- DateType (Dropdown) holding the parameter type
Label Value
Days 0
Weeks 1
Months 2
...
After that i would do the calculation at the query of stored procedure
level with something like this:
SELECT *
FROM Table1
WHERE Table1.DateField >= CASE
WHEN @.DateType = 0 THEN
DATEADD(dd,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 1 THEN
DATEADD(wk,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 2 THEN
DATEADD(mm,CAST(@.DateNumber AS
INTEGER),GETDATE())
END
HTH,
Eric|||WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))|||This will work, or, just set the parameter type as integer instead of
string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <eric__brochu@.hotmail.com> wrote in message
news:1105996187.872921.265900@.f14g2000cwb.googlegroups.com...
> WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))
>