Link to home
Create AccountLog in
Avatar of JC_Lives
JC_LivesFlag for United States of America

asked on

SQL Server - Cleaning up bad date data

Hi experts,

I'm trying to bring in about 3 million rows of date data. The problem is, I'm finding that some of the data is bad, and causes functions to fail. So when i bring it into my database, I'd like to clean it up a little in the process. This code is my working version for the cleanup process:

DECLARE @table table (dte INT, formdte datetime)
insert into @table (dte, formdte)
select top 1000
dtemtg,
case
when DTEMTG = 0 then null
when DTEMTG < 19100101 then '1/1/1910'
else convert(datetime, convert(char(8),DTEMTG)) end as formdte

from lmoutput.dbo.firstandsecondcombined (nolock)
where DTEMTG is not null

order by DTEMTG

select * from @table

My problem is, even this code fails at the "else" portion of my case statement. Maybe because I'm not finding all the scenarios. Also attached is the top 1000 dtemtg values from the firstandsecondcombined table for your reference. Any ideas? Thanks in advance!    
weird-date-data.xlsx
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
oops, left off a closing paren ) at the end of the ISDATE line above.
ah, that's because I left it in the line below, where it doesn't belong.
else convert(datetime,DTEMTG,112)
Avatar of JC_Lives

ASKER

That's great. The isdate was all I needed... thanks!