sql server: what's wrong with my date data?

Question

i have a column with dates, but it is a varchar:

8/31/2010 9:48
8/31/2010 9:49
8/31/2010 9:51
8/31/2010 9:52
8/31/2010 9:55
8/31/2010 9:59
8/31/2010 10:11
8/31/2010 10:13
8/31/2010 10:16
8/31/2010 10:37
8/31/2010 10:42

i made sure that none of these will be a BAD date:

SELECT *
FROM qcvalues.dbo.batchinfo
WHERE ISDATE(reporttime) <> 1

this returned 0 results

question: i need to return dates between a certain range:

select rowid from qcvalues.dbo.batchinfo where CONVERT(DATE, Substring( reporttime, 1, LEN(reporttime)), 103)
    between cast('2010-08-01' as datetime) and CAST('2010-08-31' as datetime)

and i am getting this error;

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

what is wrong with my conversion?

Answer 1

put SET DATEFORMAT MDY before your query.

Answer 2

If you need to store dates then use a datetime column in the future

does this work?

WHERE CONVERT(DATE,RTRIM(reporttime))
BETWEEN '2010-08-01' and '2010-08-31' 

If not use SET DATEFORMAT MDY before running it

And if you have to store it in a varchar column then use YYYYMMDD format...that way you can do

WHERE reporttime like '201008%' if you want August 2010

Answer 3

Remember, this CAST('2010-08-31' as datetime) will have its time portion as 00:00.

Consider casting your varchar data as smalldatetime, and being specific about the boundaries of times in your between. No need to be converting, substring, etc. Just one CAST will do.

Consider this as a potential solution:

SELECT rowid from qcvalues.dbo.batchinfo 
WHERE CAST(reporttime as smalldatetime)
BETWEEN '2010-08-01' AND '2010-08-31 23:59'

Answer 4

This will strip out the time portion too

select rowid 
    from qcvalues.dbo.batchinfo 

    Where cast(floor(cast(cast(reportTime as datetime)as float))as datetime)

     between    cast('2010-08-01' as datetime)
     and        cast('2010-08-31' as datetime)

Answer 5

This will solve your problem:

select rowid
from qcvalues.dbo.batchinfo
where
   CONVERT(DATE, reporttime, 101) >= '20100801'
   -- style 101, not 103
   -- also notice date conversion invariant format YYYYMMDD with no separators
   AND CONVERT(DATE, reporttime, 101) < '20100901'
   -- using BETWEEN with an end date of '8/31/2010' will skip
   --   times between '8/31/2010 00:00:00.003' and '8/31/2010 23:59:59.997'

Try this to see what the problem is:

select convert(datetime, '8/31/2010 9:48', 103)
select convert(datetime, '8/31/2010 9:48', 101)