How can I pass DATETIME type from a report in reporting services to a stored procedure?

I have a reporting services report which has a dataset based on the SQL snippet below.

I have designed the report in reporting services and have set the date parameters to DateTime (so i get the calendar datepicker).  However, I cannot run the report because it says the datetime is formatted incorrectly.

Can anyone help?




@locationID NVARCHAR(15),@fromDate DATETIME,@toDate DATETIME
SELECT *
FROM Statistics
WHERE [Location ID]=@locationID AND DateViewed BETWEEN @fromDate AND @toDate
-- The following query works --
SELECT *
FROM Statistics
WHERE [Location ID]='LOC001' AND DateViewed BETWEEN '20041231' AND '20061231'

Open in new window

almuallimAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
catherinelouiseConnect With a Mentor Commented:
Do you have SQL 2005 SP1 installed?  Seems like this was a bug that was resolved:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1
0
 
Jagdish DevakuSr DB ArchitectCommented:
Hi,

Please refer to the following link, which might help you,....

http://msdn.microsoft.com/en-us/library/ms189794.aspx

all the best...
0
 
almuallimAuthor Commented:
Hiya,

I don't quite understand what you mean.  Datediff will find the difference between two dates; however, i'm having a problem passing datetime value from my report to my stored procedure.   Please clarify.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jagdish DevakuSr DB ArchitectCommented:
Sorry for my above post...

I giving you an example on how to retrieve the data betwwen two dates... i think this will work...

Declare @vdate1 varchar(50);
Declare @vdate2 varchar(50);


set @vdate1 = getdate(); --todays date
set @vdate2 = getdate()+100; -- date after 100 days

--Sql statement to compare 2 dates

SELECT * FROM Mytable
WHERE (CONVERT(varchar,DateColumn,style) >= @vdate1 AND
CONVERT(varchar,DateColumn,style) <= @vdate2)
0
 
almuallimAuthor Commented:
Hi,

I know what you mean.  But I want to use the datepicker in SQL Reporting Services.  That can only be used when the parameter if of type DATETIME.  If i change it to string then the datepicker does not appear (which is bad from a user perspective).

0
 
catherinelouiseCommented:
Keep your parameter as a datetime so you have the SQL picker.  I think you then need to use a convert in your code such as:
AND DateViewed BETWEEN CONVERT(CHAR, @fromDate,112) AND  CONVERT(CHAR, @toDate,112)
0
 
almuallimAuthor Commented:
Closer...
Now, I have changed my query as per above.  When i test the query in the Data tab of report designer it works fine(with english formatted date).  However, when i prevew the report and try and use the datepicker it says parameter not in correct format.

"The value provided for the report parameter 'fromDate' is not valid for its type"
0
 
almuallimAuthor Commented:
Installing it now!  Will let you know how it goes...
0
 
catherinelouiseCommented:
Fingers crossed!  If you go the whole way through the thread you'll find there were varying solutions.  If you still have the same problem, check out the thread originators final comment:

Manually edit the ReportViewer.aspx on the server to add Culture="en-GB" to the @page directiveso that the date picker puts the date into the date box the UK way round.

Use the Internet Explorer lanugage settings to put en-GB at the top of the language list on my client machines (I've never neaded to press that button before today).
0
 
almuallimAuthor Commented:
Worked a treat.  Funny how it's always the simplest of solutions.  Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.