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?

gbn · Accepted Answer · 2010-01-31 16:59:36Z

2

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

answered Jan 31, 2010 at 16:59

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

itsatrp Over a year ago

Doh! That was an easy fix. Thanks.

Paul Creasey · Accepted Answer · 2010-01-31 16:59:51Z

0

have you tried without the single quotes?

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

answered Jan 31, 2010 at 16:59

Paul Creasey

28.9k10 gold badges60 silver badges91 bronze badges

Comments

Guffa · Accepted Answer · 2010-01-31 17:00:42Z

0

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

answered Jan 31, 2010 at 17:00

Guffa

703k112 gold badges760 silver badges1k bronze badges

Comments

Ravi Vanapalli · Accepted Answer · 2010-01-31 17:02:33Z

0

just remove '' quotes

answered Jan 31, 2010 at 17:02

Ravi Vanapalli

9,9703 gold badges36 silver badges43 bronze badges

Collectives™ on Stack Overflow

Error comparing dates in SQL query

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related