Date question

Question

Date question

sankar soma

SSC Enthusiast

Points: 147
More actions
November 30, 2006 at 9:51 am

#90767

Hi,
I need some help with a query to build a report using sqlserver reporting services..
The query I have is :
SELECT ecommerce.dbo.order_header.submitted_date
FROM ecommerce.dbo.order_header
WHERE ecommerce.dbo.order_header.submitted_date = @submitted_date
My question is: Here the @submitted_date is got by the user at run time(tht shud be clear). @submitted_date is of datatype datetime.
If i give a date as 1/11/2005 as input, it returns zero rows....because the date is entered as 1/11/2005 11:45:00 AM in the database. if i enter it as it is stored exactly i get 1 row as output.
I want/need the query to return all the rows if i just enter 1/11/2005. How would i do it?. Your help is appreciated.
thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DaveItz SSCrazy Points: 2146 More actions · Answer 1

If you want to stick to the current structure, you can do:

WHERE convert(datetime, convert(varchar, ecommerce.dbo.order_header.submitted_date, 101)) = @submitted_date

(there's probably a more elegant way of doing it that I'm blanking on

maybe using a:

WHERE DateDiff(ss,@submitted_date, ecommerce.dbo.order_header.submitted_date) between 0 and ((24*60*60)-1)

OR using a: (I'd probably go with this one)

WHERE ecommerce.dbo.order_header.submitted_date >= @submitted_date
  and ecommerce.dbo.order_header.submitted_date < dateadd(d,1, @submitted_date)

)

However, if you want, you can change the parameters to use @submitted_date_start and @submitted_date_end. You can, then, continue using the original field. It is probably more index-friendly.

sankar soma SSC Enthusiast Points: 147 More actions · Answer 2

Thanks dave. worked like a charm. I am also thinking about the second option you mentioned...Both the options work.I dont know how they need it..anyways,Thanks a lot!

DaveItz SSCrazy Points: 2146 More actions · Answer 3

No problem.

Take a look at the third(?) option mentioned above.

WHERE ecommerce.dbo.order_header.submitted_date >= @submitted_date
  and ecommerce.dbo.order_header.submitted_date < dateadd(d,1, @submitted_date)

sankar soma SSC Enthusiast Points: 147 More actions · Answer 4

i have another question now..dont know if this is a right place to post..

How would one do a parameter validation in sql reporting services. For ex: If I have a date start and date end parameters to be entered by the user and the report gets generated depending on the user input. How would i validate the user entered date is acceptable?..like I dont want the user to enter dd/mm/yy instead of mm/dd/yyyy..

Thanks,