Hi all,

Batchdate comes in as string format (07/07/2011). I tried but for some reason am not getting any results.

Any ideas?

ALTER PROCEDURE  dbo.CouponReportbyScheme
	@BatchDate varchar(20) 

	declare @myDate  datetime
	select  @myDate =  convert( datetime, @BatchDate) 
         Select * from Coupons where CouponGenerationDate=@myDate

Recommended Answers

What is the expected result ?

Jump to Post

All 3 Replies

What is the expected result ?

Datetime format in SQL Server depends on your profile.
If you want to be sure that the date passed will be read correctly then use the


For details read here: http://msdn.microsoft.com/en-us/library/ms189491.aspx

Any other way will be user specific and probably will be a lot of trouble.

Also depends on what the datatype of column CouponGenerationDate is in the table. If it is not a datetime, you might have problems. If it IS a datetime, and if it contains a "time" portion, that might mess up your comparison, too.

Also, your CONVERT statement needs to have a format, as in

CONVERT(datetime, @BatchDate, 101)

To be safe, if you can't change the datatype on your table, use CONVERT on both parts of the comparison, like so:

Select * from Coupons 
where CONVERT(datetime, CouponGenerationDate, 101) = CONVERT(datetime, @BatchDate, 101)

Hope this helps!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.