Replace Errors with NULL using Convert

I have a table with a varchar field that for the most part contains valid
dates ie (mm/dd/yyyy). There are some items that are not dates. I would
like a SQL statement that converts the varchar to a datetime and where there
is an error for a particular field will return a null for that field.
For example if my table contains the following items in Field1:
2/1/2005
1/1/2004
other data
3/1/2005
The query should return:
2005-2-1 00:00:00
2004-1-1 00:00:00
NULL
2005-3-1 00:00:00
If I use:
SELECT convert(datetime, Field1) as Field1
FROM table1
Then I get a conver error. Any ideas or suggestions would be helpful.
Thanks!
Dan,
See if this works:
select
case when ISDATE(Field1) = 1
then cast(Field1 as datetime)
else NULL end
from table1
If you can't live with the fact that some garbled
data may convert unexpectedly (the string '110919'
will convert to September 19, 2011, for example),
you'll have to do some pattern matching of your own
as well, such as
case when Field1 like '%/%/%' and Field1 not like '%/%/%/%' ...
and isdate(Field1) = 1 then ...
Steve Kass
Drew University
Dan wrote:

>I have a table with a varchar field that for the most part contains valid
>dates ie (mm/dd/yyyy). There are some items that are not dates. I would
>like a SQL statement that converts the varchar to a datetime and where there
>is an error for a particular field will return a null for that field.
>For example if my table contains the following items in Field1:
>2/1/2005
>1/1/2004
>other data
>3/1/2005
>The query should return:
>2005-2-1 00:00:00
>2004-1-1 00:00:00
>NULL
>2005-3-1 00:00:00
>If I use:
>SELECT convert(datetime, Field1) as Field1
>FROM table1
>Then I get a conver error. Any ideas or suggestions would be helpful.
>Thanks!
>
>