Selecting data between two dates

[elizann10]

I have a sql database with date in the format
2006-03-10 00:00:00.000

I have the following select statement

select * from database where date >= 1/5/2008 and date <= 7/3/2011

which is in the format dd/mm/yyyy

I am however getting dates in 2006 in my result.

How do i convert the date in the database to same format?

Thanks

[r937]

put quotes around your dates in the sql

[elizann10]

Sorry yes i did have quotes but it is still giving a wrong result

Thanks

[r937]

could you show the revised query please

[elizann10]

select * from database where date >= '1/5/2008' and date <= '7/3/2011'

[r937]

microsoft sql server should have no problems with those date strings

try it like this --

... where date >= '2008-01-05' and date <= '2011-07-03'

if this still returns results in 2006 then something else is wrong

[elizann10]

Tried that, but still the same result. Not sure whats the problem

Thanks

[siteguru]

I presume there's no issue in SQL Server in using date as a field name?

[elizann10]

No not that i have seen, I am using sql server 2005

[r937]

what is the datatype of this date column?

also, why is this an asp question? have you tested the query outside of asp?

[elizann10]

its a datetime

I put it in asp because the program i am writing is in asp, but i guess it could go in a sql forum

Thanks

[r937]

okay, if it's datetime, have you tested the query outside of asp?

because all versions of the query we've seen in this thread should definitely work correctly

[elizann10]

I tested it in the database itself and got the same result

Thanks

[r937]

in that case, you should contact microsoft, they will be interested in learning of this bug in their database engine