CODEWITHSUNDEEP
sqlsql-servert-sqldatecasting
itsatrp
itsatrp

Reputation: 103

Error comparing dates in SQL query

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?

Upvotes: 0

Views: 372

Answers (4)

Ravi Vanapalli
Ravi Vanapalli

Reputation: 9942

just remove '' quotes

Upvotes: 0

Guffa
Guffa

Reputation: 700342

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());

Upvotes: 0

Paul Creasey
Paul Creasey

Reputation: 28834

have you tried without the single quotes?

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

Upvotes: 0

gbn
gbn

Reputation: 432261

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

Upvotes: 2

Related Questions