1

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
Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.
That error is from this CONVERT(DATE, reporttime, 103) - Do you have a bad value not showing in your post example? Bobs solution works for me.
Yeah, I saw that too. In your solution, try changing each date string to sue the CONVERT, CONVERT(DATE, '2010-07-01', 103). Or, you can try changing your first CONVERT, to CONVERT(DATE, ReportTime, 101)`. The US format works for me, so it may be related to region.
i cannot do that, as you can see reporttime is in this format: 1/2/2010 8:25 AM
I saw the same issue with the CONVERT(DATE, reporttime, 103). The orginal solution works when I change 103 to 101.
1

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

Improve this answer

5 Comments

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.
problem i think is that i ahve seconds in there, i think i need to take everything before the first space
This works when I tried it: select convert(date, '1/2/2010 8:13:02 AM', 101)
for me as well, but when i do select convert(date,reporttime, 101) from batchinfo, it gives me the level 16 error
You've got some bad data in that varchar column then. Another example of why it's so important to use appropriate data types.
-1

To find your bad data try this:

SELECT *
FROM batchinfo 
WHERE IsDate(reporttime) = 0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.