Math overflow error converting to DATETIME from large textfile

[nyccrystal]

I have a file of approximately a million records that was imported from a mainframe file. I'm trying to convert a field that's in a format like 20070530 - ie, YYYYMMDD, in order to be able to do date calculations on it. I don't care how it looks, I just care if internally it's a real datetime field. I'm trying to insert it into a new file using CONVERT(datetime,date_billed,101), but I keep getting a math overflow error. Is this because of the number of records, or what? The record length is only 50 characters.

 

[LNBruno]

Is it failing on ALL or some? A quick Google shows it could be a lot of things. You might want to check this out:

http://www.karaszi.com/sqlserver/info_datetime.asp

< M!ke >
"Believe, but verify.

 

[gmmastros]

That format YYYYMMDD is the perfect format to use for sql server. I would suggest you remove the ,101 from your convert statement.

Also, you may want to look into the IsDate function. It returns 1 if you have a valid date, otherwise it returns 0.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[harebrain]

Date style 101 is mm/dd/yyyy. Try ISO style 112 instead.

 

[nyccrystal]

I've tried the above suggestions, and still keep getting the overflow error. Could it have anything to do with the number of records? Does SQL Server 2005 have some sort processing limitation?

 

[gmmastros]

Do you have this data in a table already?

Also, double check the data type you are trying to import in to. There are 2 data types that can store data. There's the DATETIME data type, but there's also a SmallDateTime. The SmallDateTime data type has a smaller range, so you could be having problems with that.

Take a look at this: faq183-6419

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nyccrystal]

Hooray, it finally worked, using style 120:

INSERT INTO CISMast
([Out_date_Billed])

select
convert(datetime,out_date_billed,120)
from cis_newfile
where isdate(out_date_billed) = 1

Thanks, guess it was just a matter of finding the right combo of everything.