Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Msg 542: An invalid datetime value was encountered. Value exceeds the year 9999 Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 10:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:32 AM
Points: 315, Visits: 193
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
Posted Thursday, December 20, 2012 10:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 3,110, Visits: 11,529
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
Posted Thursday, December 20, 2012 12:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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
Posted Thursday, December 20, 2012 8:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:32 AM
Points: 315, Visits: 193
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
Posted Friday, December 21, 2012 12:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 »

Add to briefcase

Permissions Expand / Collapse