Conversion of Char to SmallDateTime

I have a column that has date information in it in the following
format: yyyymmdd I want to convert this information into and actual
SmallDateTime column so I can use it for comparison etc. Some of the
dates that are stored in this data are either blank or invalid dates
because of bad user input.
select
cast(left(inv_dt1,4) + '-' + right(left(inv_dt1,6),2)+'-'+
right(inv_dt1,2) as smalldatetime) as inv_dt
into newtable
from origtable
If I run that code, it errors out on the invalid fields. Is there a
way to tell SQL to just NULL the field if it is invalid in any way and
continue on?
Thanks,use the isdate function
create table WasabiTable (inv_dt1 varchar(23))
insert WasabiTable values ('ababababa')
insert WasabiTable values ('20060101')
insert WasabiTable values ('20060299')
insert WasabiTable values (NULL)
insert WasabiTable values ('20060401')
insert WasabiTable values ('20050331')
select
case
when isdate(inv_dt1) = 1 then convert(smalldatetime,inv_dt1)
else null
end onsetdate
from WasabiTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On 30 May 2006 12:10:08 -0700, "SQL Menace" <denis.gobo@.gmail.com>
wrote:

>use the isdate function
Just to add to that, be aware the ISDATE tests if the string will
convert to a valid DATETIME datatype. Valid dates for DATETIME are
from January 1, 1753 through December 31, 9999. Valid dates for
SMALLDATETIME are from January 1, 1900, through June 6, 2079. So,
while it may be unlikely, it is possible for a string to satisfy the
ISDATE test yet still fail the conversion to SMALLDATETIME.
Roy Harvey
Beacon Falls, CT