Report Parameters - Decimal vs. Float

My dataset returns data from a range of effective dates for a single
month. In the database, these dates are unfortunately not of type
datetime, but are decimal and are in the YYYYMMDD format.
I'm having trouble getting the report to execute because at the query
level, the datatype has to be decimal. I need to parameterize these
dates, but there is no decimal datatype for parameters.
I have a dataset that generates the necessary dates based on the type
of report the user wants (60/90/120 day) (if they want a 60-day report,
the effective date = 2 months from the first day of the current month,
etc).
SELECT DISTINCT
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '01') +
@.period_additive AS effdte_low,
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '31') +
@.period_additive AS effdte_high
FROM dbo.t_policy
WHERE YEAR(GETDATE()) - 1 = LEFT(CAST(effdte AS varchar), 4) AND
MONTH(GETDATE()) = SUBSTRING(CAST(effdte AS varchar), 5, 2) AND
LEFT(policy, 3) LIKE @.dept_cd AND LEFT(policy, 3) <> 'LPA'
What would the appropriate code be to do this within the Report
Parameters dialog instead? It seems like I'd have a better chance of
success if it were done that way.
Thanks!
MikeNo matter what, I get this error at runtime (Preview mode):
--
Processing Errors
--
An error has occurred during report processing.
Cannot read the next data row for the data set ds_main.
Arithmetic overflow error converting expression to data type datetime.
--
OK
--|||I think I fixed it...
Even though the database type is decimal, when searching by that range,
I have to put single quotes around it. Therefore, I converted the date
type back to a varchar and changed the parameter data types to string.
Now the report runs.
But there should be a more straightforward way to do this within the
Report Parameters dialog box, shouldn't there?sql

Report parameters

I have a datetime parameter. Also I have a listbox with available jobs to
report for. I want to filter the listbox on the selected date. How can I do
that? Thanksuse the date-time parmeter in the sql for the dropdownlist
"Markgoldin" wrote:
> I have a datetime parameter. Also I have a listbox with available jobs to
> report for. I want to filter the listbox on the selected date. How can I do
> that? Thanks|||I am trying that but when I switch to preview I am getitng an error:
The report parameter â'jobâ' has a DefaultValue or a ValidValue that depends
on the report parameter â'RunDateâ'. Forward dependencies are not valid.
And here is my SQL for Job parameter:
select * from udf_GetJobs(@.RunDate)
order by job
"Antoon" wrote:
> use the date-time parmeter in the sql for the dropdownlist
> "Markgoldin" wrote:
> > I have a datetime parameter. Also I have a listbox with available jobs to
> > report for. I want to filter the listbox on the selected date. How can I do
> > that? Thanks|||Switch the order of your parameters. You can do that where it lists the
parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Markgoldin" <Markgoldin@.discussions.microsoft.com> wrote in message
news:A2179882-FB0F-4293-98B7-3390E07299BD@.microsoft.com...
>I am trying that but when I switch to preview I am getitng an error:
> The report parameter 'job' has a DefaultValue or a ValidValue that depends
> on the report parameter "RunDate". Forward dependencies are not valid.
> And here is my SQL for Job parameter:
> select * from udf_GetJobs(@.RunDate)
> order by job
> "Antoon" wrote:
>> use the date-time parmeter in the sql for the dropdownlist
>> "Markgoldin" wrote:
>> > I have a datetime parameter. Also I have a listbox with available jobs
>> > to
>> > report for. I want to filter the listbox on the selected date. How can
>> > I do
>> > that? Thanks

Report parameter with datetime

Hi,

Anyone can help me?

I created a store proc with @.startdate and @.enddate

in query analyser - I run it as Exec SP_Admission '2006/01/01','2006/01/25' and it is ok.

Then I created a report in VS2005 and set the parameter as datetime in report parameter.

if I manually force to fill startdate and enddate with ‘yyyy/mm/dd’ format in preview, it works.

However if I choose the date from the given calendar(Date Time Picker), it doesn’t work

it is said that "The value provided the report parameter 'enddate' is not valid for its type.

Of course it not valid because it always comes with ‘dd/mm/yyyy’ format

I didn't setup the date time picker, it will automatically when I choose datetime as datatype.

any idea what should I do?

thanks,

Susan

Hi Susan,

not exactly brilliant, but

Exec SP_Admission convert(datetime,'12/10/2006',103),convert(datetime,'12/11/2006',103)
should work in your case, so put your parameter instead of '12/10/2006'
|||

Thanks...

someone suggested that in my SP -

I declare as varchar then in where statement I convert it as you mention.

however in report para - I can't set it as datetime but set to string.

it works ok but I can't use the date time picker .

well at least works that way...

Thanks so much