SQL Server 2008 Casting varchar to datetime

Question

I'm running into an issue casting a VARCHAR in one table into a DATETIME and then using it in a comparison. The format ('2010-09-02 10:38:31.000') seems to be correct on all of the records, and I can select the data without any problems. However, if I try to use the new datetime in the WHERE clause, I get the error: Conversion failed when converting date and/or time from character string.

So just to be clear, this works:

SELECT Field1, Field2, CAST(Field3 AS DATETIME) AS DateTaken FROM MyTable;

That returns all of the data as expected, and there are no null dates. The following fails with the previously mentioned error.

SELECT Field1, Field2 FROM MyTable WHERE CAST(Field3 AS DATETIME) > @startDate;

I tried using a view to retrieve the data, but it had the same error. Next I tried creating a temporary table for the data like so...

SELECT Field1, Field2, CAST(Field3 AS DATETIME) AS Field3 INTO _MyTable;
SELECT Field1, Field2 FROM _MyTable WHERE Field3 > @startDate;

Which works successfully.

All of my searching just leads to incorrectly formatted dates and / or regional settings causing havok which does not appear to be the cause in my case. I'd rather not create a table every time I need to use a DATETIME cast, so what am I doing wrong here? Help is much appreciated!

Solution 1

edit # 3..............

  select * from test 
  where case isdate(field3) when 1 
        then CAST(field3 as datetime) else null end > @startdate

edit # 2..............

I did a test on my db...

declare @startdate datetime

select @startdate = '09/1/2010'

Create table test (field1 varchar(50), field2 varchar(50), field3 varchar(50))


insert into test
select 'test' as field1, 'test' as field2, '2010-09-02 10:38:31.000' as field3

insert into test
select 'test2' as field1, 'test2' as field2, '2010-09-02 10:38:31.000' as field3

insert into test
select 'test3' as field1, 'test3' as field2, '2010-10-02 10:38:31.000' as field3

select * from test where CAST(field3 as datetime) > @startdate

select * from test where Convert(datetime, field3) > @startdate

drop table test

Results:

test    test    2010-09-02 10:38:31.000
test2   test2   2010-09-02 10:38:31.000
test3   test3   2010-10-02 10:38:31.000


test    test    2010-09-02 10:38:31.000
test2   test2   2010-09-02 10:38:31.000
test3   test3   2010-10-02 10:38:31.000

changing the startdate param to 10/1/2010 Results:

test3   test3   2010-10-02 10:38:31.000

test3   test3   2010-10-02 10:38:31.000

1st response................. Did you try Convert?

SELECT Field1, Field2 FROM MyTable WHERE CONVERT(DateTime, Field3) > @startdate;

Solution 2

Consider forcing specific dateformat using SET DATEFORMAT ymd