Query returns incorrect range

[arbytech]

Hi everyone!

I have inherited a SQL database that works OK for the most part. However I have also inherited a VB program that will query the database for a range of dates and then display those dates in a report.

The problem happens when you try and query a range for example of 04-25-2005 thru 04-26-2005. The query returns all the instances of 04-25-2005 and 04-26-2005 but also returns instances of 04-25-2004 and 04-26-2004.

The table queried is setup with the date field type as
varchar(15). The query string is:

select date from table where date >= '04-25-2005' and date <= '04-26-2005';

I have tried changing the syntax of the select statement and tried changing the field type to varchar(20) but no luck.

Any ideas or comments would be appreciated!!!

RB

 

[dhulbert]

Its seeing the values as strings as such

04-26-2004 is larger then 04-25-2005

you will need to change the field to be a date field before this works correctly.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[arbytech]

Thanks dhulbert it makes sense now!

Is there any way to convert the current data in the date field to use the date type? I thought that SQL used dates in a yyyy-mm-dd format and all the dates in my db are in the mm-dd-yyyy format.

Thanks again!

 

[MDXer]

the column datatype is string so SQL Server treats your criteria as a string. If you are unable to change the column data type then you can cast the string to dtm.

select date from table where Cast(date) >= '04-25-2005' and date <= '04-26-2005'

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

select date from table where Cast(date) BETWEEN '04-25-2005' and '04-26-2005'

is an alternate syntax to use.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[DotNetGnat]

date is a reserved word. i would suggest you to use other name for your field...

-DNG