select from db by date interval

[cipibs]

Hy,

I have a problem.

I use tis query to select from a database:(asp line)
sql="Select DISTINCT * from TAble where Data > '1/" & from_D & "' and Data < '31/" & to_D & "' order by Data;"

where "from_D" is like "01/2001"
and "to_D" is like "02/2002"

I used also "where data between date1 and date2"

The results are the same(not ok)
I get duplicates, and records from 2003.

I think the problem is the format of the date. In the database the date is introduce in a text field in dd/mm/yyyy format.
So my guess is that "the select does not know what is selecting" :)

Thanks, and hope you can help

[aspdaddy]

If you are storing the date as text, you are going to have to cast it to a date or numeric type, before using = < between etc,

Look at what functions are provided in your version of SQL, Functions like to_int, to_date etc.

Something like this approach should work though for dates inclusive :

where to_date(strDateField) >= to_date(strLowerBound) and
to_date(strDateField) <= to_date(strUpperBound)

>Data > '1/"

This is probably just using the ASCII values of Data and '1/' as you are asking for a string comparison, thats why the results seem wrong.

Some good info on dates/asp here
[aspfaq.com...]