Member Avatar for 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
  • 4 Contributors
  • 3 Replies
  • 119 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by BitBlt
Member Avatar for debasisdas

What is the expected result ?

Member Avatar for 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.

Member Avatar for 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!

Be a part of the DaniWeb community

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