Date Compare Help

[Tracy]

Dates are stored in my SQL table as a TEXT field in the
YYYYMMDD format. I needed to add months, so I converted
the dates to MM/DD/YYYY in a query and added the months
using the DateSerial command. This query's results are
the input to another query that uses the BETWEEN date
comparison on the calculated MM/DD/YYYY fields but the
results are all wrong. The second query should only
produce a recordset with dates between 06/01/2004 and
06/30/2004. I have records in the result set with
06/15/1999 as the date from my BETWEEN comparison. Any
suggestions would be greatly appreciated!!!

 

[Tim Ferguson]

Dates are stored in my SQL table as a TEXT field in the
YYYYMMDD format.

Oh dear: well at least they are sortable and searchable..

I needed to add months, so I converted
the dates to MM/DD/YYYY in a query

now they are not even that. What about using a proper DateTime value?

and added the months
using the DateSerial command.

And does this give the correct answer so far?

This query's results are
the input to another query that uses the BETWEEN date
comparison on the calculated MM/DD/YYYY fields but the
results are all wrong.

If you are using text strings, that is hardly surprising. Even if you are
using proper DateTimes, between is not as good as a pair of > and <
comparisons.

The second query should only
produce a recordset with dates between 06/01/2004 and
06/30/2004. I have records in the result set with
06/15/1999 as the date from my BETWEEN comparison. Any
suggestions would be greatly appreciated!!!

But "06/01/2004" does come before "06/15/2004" because the 0 character is
smaller than the 1, which in turn is smaller than the 3. Computers are
stupid and they do exactly what you ask them to.

If you really want to play with text strings, use the ISO format yyyy-mm-dd
or at least the sensible equivalent which is what is in the original
database.

WHERE '20040601' <= MyDateString
AND MyDateString <= '20040615'

Hope that helps


Tim F