Msg 542: An invalid datetime value was encountered. Value exceeds the year 9999

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Data Corruption (SS2K8 / SS2K8 R2) » Msg 542: An invalid datetime value was...

Msg 542: An invalid datetime value was encountered. Value exceeds the year 9999

Rate Topic

Display Mode

Topic Options

Author

Message

David Paskiet

Posted Thursday, December 20, 2012 10:15 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:02 AM
Points: 313, Visits: 183

Guys:

Some how an invalid or corrupt data ended up in the system. Presumably this happened when the database was upgraded from a legacy system. I know that in the old systems a bulk load process would allow bad dates to come in. It was a bug that was ignored by the dev team.

I am trying to identify the rows in order to identify/correct the data but am having absolutely NO luck. I cant convert it nor does the isdate function work without error'ing out. Any suggestions? I am out of options.

Thanks!

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Post #1399047

Michael Valentine Jones

Posted Thursday, December 20, 2012 10:51 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:33 PM
Points: 2,944, Visits: 10,509

What is the datatype of the column containing the bad data?

If it is not a DATE, DATETIME, DATETYPE2, or SMALLDATETIME datatype, what it the format of the data?

If you really suspect database corruption, have you run DBCC CHECKDB against that database?

DBCC CHECKDB ( MyDatabase )
WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY;

Post #1399067

GilaMonster

Posted Thursday, December 20, 2012 12:23 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

Run CheckDB with the Data_Purity option. For Resolving the error, see the section in this article on data purity errors and the kb article it links to.
http://www.sqlservercentral.com/articles/65804/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399085

David Paskiet

Posted Thursday, December 20, 2012 8:13 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:02 AM
Points: 313, Visits: 183

I found the issue using the checkDB function with data purity which is what caused me to post. What I could not do was identify the rows themselves in the normal fashion that i do. Instead I jumped through hoops and did the RBAR thing trying to find the actual rows. I pretty much opened the table and looked at each row (70K rows) using the GUI rather than a simple query.

There has to be an easier way! I am all ears and open to a better way!

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Post #1399157

GilaMonster

Posted Friday, December 21, 2012 12:37 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

David Paskiet (12/20/2012)

There has to be an easier way! I am all ears and open to a better way!

See the kb article referenced in the article I mentioned.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399226

« Prev Topic | Next Topic »

Permissions