not able to fetch record for string as a date type variable in sql 2005

my problem is: If i run this it returns the result

declare @dummy DATETIME
set @dummy ='10-20-2008'

SELECT * FROM associate WHERE dateofbirth = @dummy

But if I remove the quotes of the dummy var value then it returns null.

declare @dummy DATETIME
set @dummy =10-20-2008

SELECT * FROM as开发者_开发百科sociate WHERE dateofbirth = @dummy

Without quotes means that expression is bigint.In your second example @dummy=10-20-2018=-2018. That means that your date will be 2018 days sooner than 01-01-1900 (23-06-1894). If you run query:

select DATEDIFF(DAY,'01-01-1900',@dummy)

You will get -2018

For SQL Server 2005 when using dates only, the only safe datetime string is yyyymmdd. And they must be delimited in single quotes too

So you have to use set @dummy ='20081020'

Example:

set language british
declare @dummy DATETIME
set @dummy ='10-20-2008' --fail

This is working as intended. You need to enclose a date with quotes if you are going to write it out like that. You could also build a date using one of the SQL functions.

** USE THIS**

Convert both the comparabilities to VARCHAR and then compare it

convert(vrachar(12),@dummy,101) = convert (varchar(12),'04/10/2008',101)