selecting data between a certain date range where date range is a varchar

Question

i have a varchar reporttime that has the date stored. dont ask me why it is a varchar

i am doing this to get data between two dates:

select rowid 
 from batchinfo 
where CONVERT(DATE, reporttime, 103) between '2010-07-01' and '2010-07-31'

and it is not working

what am i doing wrong?

btw this is what the data looks like:

rowid   datapath                                                        analysistime       reporttime
4695    F:\MassHunter\DATA\6897_Pan_1\QuantResults\6897_Pan_1.batch.bin  1/2/2010 8:13 AM   1/2/2010 8:25 AM
4696    F:\MassHunter\DATA\6897_Pan_2\QuantResults\6897_Pan2.batch.bin   1/2/2010 8:21 AM   1/2/2010 8:33 AM
4697    F:\MassHunter\DATA\6903_Pan_1\QuantResults\6903_P1.batch.bin     1/2/2010 9:41 AM   1/2/2010 9:46 AM
4698    F:\MassHunter\DATA\6903_Pan_2\QuantResults\6903_Pan2.batch.bin   1/2/2010 9:50 AM   1/2/2010 9:57 AM
4699    F:\MassHunter\DATA\6915_Pan_1\QuantResults\6915_pan1.batch.bin   1/2/2010 10:09 AM  1/2/2010 10:33 AM

Answer 1

First off, I assume your sample data is not supposed to qualify, true?

Second, you may still be doing a alphanumeric comparison, not a datetime comparison. Try this to force the datetime comparison.

select rowid 
from batchinfo 
where CONVERT(DATE, reporttime, 103)
    between cast('2010-07-01' as datetime) and CAST('2010-07-31' as datetime)

The CAST operator is similar to the CONVERT, which can also be used.

Answer 2

CONVERT style 103 is dd/mm/yyyy (European format) so you've got your month and day reversed. Try using 101 instead.

Answer 3

To find your bad data try this:

SELECT *
FROM batchinfo 
WHERE IsDate(reporttime) = 0