varchar, datetime and single quotes issue

[NormRumac]

I got a problem with an sql statement:

In a table there is a field of type varchar. That field ,called datearrival, contains human readable dates such as "March 4th 2004" (no quotes).

I want to select all items for which the number of days between datearrival and the current date is > 50.

So I try the following sql statement:

1. "select id from item where datediff(dd,datearrival,getdate())>50"

Sql Server complains about not being able to convert between from varchar to datetime. How can I correct this problem.

I tried manually entering the following statement and it happens to work:
2. "select id from item where datediff(dd,'March 4 2004',getdate())>50"

Since this worked, I thought this means i may need to append single quotes to subdescription3 in my original statement, in order to get it to work. So I tried:
3. "select id from item where datediff(dd,''''subdescription3'''',getdate())>50"

but that does not work...So I'm stuck here. Is there an easy way to solve my problem??

 

[amorous]

Try this:

select id from item where datediff(dd,Convert(datetime,datearrival),getdate())>50"

hope this helps.

VJ

 

[NormRumac]

Unfortunately, I get this same error message, even with your statement:

"Syntax error converting datetime from character string

 

[donutman]

My guess is that you have a row with a date that it cannot convert. You can find it by doing something like this in the QA:
Declare @PrimaryKey int, @DateDiff int
Select @PrimaryKey=YourKeyField, @DateDiff=datediff(dd,datearrival,getdate()) from YourTable
go
Select datearrival from YourTable where YourKeyField=@PrimaryKey
-Karl

 

[donutman]

Oops! Forgot you can't use go in QA. Try it without it.
-Karl

 

[NormRumac]

donuntman...YOU ARE A GENIUS! That was the problem.
For some of the rows, the datearrival field was not input correctly (i.e. Not in the form like 'March 4 2004'). I fixed that and now the query works!

Thanks,
--Norm