Error comparing dates in SQL query

Question

Within a ASP.NET/C# class I am trying to run a query where I compare dates:

select * from table1 CreatedDate >='DATEADD(d,-500,GETDATE())';

Basically I am trying to select rows from the the last 500 days.

The problem is I am getting the following error: Syntax error converting datetime from character string.

An example of the CreatedDate field in the database is 2003-09-19 15:32:23.283 . The field is set to type = datetime, default = getdate().

If I run the query SELECT DATEADD(d,-500,GETDATE()); it returns 2008-09-17 23:41:34.710

The 2 values look the same so I am surprised am getting the error message. An idea on how I need to modify my query?

Answer 1

select * from table1 CreatedDate >= DATEADD(d,-500,GETDATE())

Lose the quotes around DATEADD(d,-500,GETDATE()). These make the expression varchar

Datatype precedence means you are trying to convert a string starting DATEADD to datetime...

Answer 2

have you tried without the single quotes?

CreatedDate >=DATEADD(d,-500,GETDATE())

Answer 3

You have put apostrophes around the dateadd expression, so it's not an expression, it's a string. That's the string that it fails to convert to a datetime value.

select * from table1 CreatedDate >= DATEADD(d,-500,GETDATE());

Answer 4

just remove '' quotes