Re: Replace Errors with NULL using Convert
From: Steve Kass (skass_at_drew.edu)
Date: 02/01/05
- Next message: Greg: "Help w/ query"
- Previous message: Dan: "Replace Errors with NULL using Convert"
- In reply to: Dan: "Replace Errors with NULL using Convert"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 01 Feb 2005 11:39:12 -0500 To: Dan <Dan@discussions.microsoft.com>
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!
>
>
>
>
- Next message: Greg: "Help w/ query"
- Previous message: Dan: "Replace Errors with NULL using Convert"
- In reply to: Dan: "Replace Errors with NULL using Convert"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
