Solved

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

Posted on 2008-06-24
10
772 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:almuallim
  • 5
  • 3
  • 2
10 Comments
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21853834
Hi,

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

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

all the best...
0
 

Author Comment

by:almuallim
ID: 21853847
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
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21853885
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
 

Author Comment

by:almuallim
ID: 21854240
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
 
LVL 5

Expert Comment

by:catherinelouise
ID: 21855820
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:almuallim
ID: 21856897
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
 
LVL 5

Accepted Solution

by:
catherinelouise earned 500 total points
ID: 21857012
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
 

Author Comment

by:almuallim
ID: 21857079
Installing it now!  Will let you know how it goes...
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 21857136
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
 

Author Closing Comment

by:almuallim
ID: 31470080
Worked a treat.  Funny how it's always the simplest of solutions.  Thanks for your help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle sql query 7 49
SQL query question 8 40
How to get time difference in minutes and seconds only between 2 dates 2 26
Access 2010 Query Syntax 5 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now