sql server mseq: Replace Errors with NULL using Convert -- sql server mseq

Replace Errors with NULL using Convert

Dan
2/1/2005 8:07:09 AM

sql server mseq: 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!

Re: Replace Errors with NULL using Convert

Steve Kass
2/1/2005 11:39:12 AM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.