Date Conversion

[PS189]

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) 
	
	)
	
AS	

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

[debasisdas]

What is the expected result ?

[adam_k]

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

SET DATEFORMAT dmy

command.
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.

[BitBlt]

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!