Can't use parameter with DateAdd Function?

Hi, I encountered a strange error today. I have an Integer parameter "hours" that I am trying to use in my SQL Query.

DATEADD(hh, @.hours, @.startTime)

It works if I have it set such as DATEADD(hh, 8, @.startTime), but I need that parameter there for what I need.

I get this error:

Error Source: System.Data
Error Message: Failed to convert parameter value from Decimal to DateTime. I tried a variety of CInt and other conversion functions to no avail.

Any ideas?

Hello,

Try this:

=DateAdd("h", CDbl(@.hours), @.startTime)

Hope this helps.

Jarret

|||Just tried using this with no luck in the following expression. My goal is to use this with a report parameter that sets the value of @.period to 1, 7, 30, 90, or 365.

SELECT *
FROM dbo.v_call
WHERE (dbo.v_call.entry_date >= DATEADD(d, CDbl(@.period), GETDATE()))

When running the query I get the following error; string was not recognized as a valid date time.

I try changing my query to

SELECT *
FROM dbo.v_call
WHERE (dbo.v_call.entry_date >= DATEADD(d, @.period, GETDATE()))

and get this error:

Failed to convert parameter value from decimal to a datetime.
|||

Try using:

Code Snippet

="SELECT * FROM dbo.v_call

WHERE (dbo.v_call.entry_date >= DATEADD(d," &

CSTR(@.period) &

", GETDATE())) "

>L<

|||

For what it is worth, I have used the following with no problems

@.Trend is an integer parameter of 30, 90, 180, 365 or 720.

Code Snippet

authopen_idx >= dateadd(d,@.Trend*-1, getdate())

Can't use integer parameter with dateadd?

Hey guys I have the following in my SQL statement:

DATEADD(hh, @.hours, @.startTime)

hours is an integer and startTime is actually a string, but the thing is that this statement works fine if I use it like this:

DATEADD(hh, 8, @.startTime)

The first version is what I need so that the user can control from a parameter and I get the following error:

Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.

Thanks!

BJ

have you tried to convert the variable?

Code Snippet

DATEADD(hh, convert(int,(@.hours)), @.startTime)

Simone

Can't use integer parameter with dateadd?

Hey guys I have the following in my SQL statement:
DATEADD(hh, @.hours, @.startTime)
hours is an integer and startTime is actually a string, but the thing is
that this statement works fine if I use it like this:
DATEADD(hh, 8, @.startTime)
The first version is what I need so that the user can control from a
parameter and I get the following error:
Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.
Thanks!
BJOn May 11, 1:50 pm, bjkaledas <bjkale...@.discussions.microsoft.com>
wrote:
> Hey guys I have the following in my SQL statement:
> DATEADD(hh, @.hours, @.startTime)
> hours is an integer and startTime is actually a string, but the thing is
> that this statement works fine if I use it like this:
> DATEADD(hh, 8, @.startTime)
> The first version is what I need so that the user can control from a
> parameter and I get the following error:
> Error Source: System.Data
> Error Message: Failed to convert parameter value from a Decimal to a DateTime.
> Thanks!
> BJ
You might need to do something like this:
DATEADD(hh, @.hours, CAST(@.startTime AS DATETIME))
- or- something like this in the report:
=Dateadd("h", Parameters!Hours.Value, CDate(Fields!startTime.Value))
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant