Corrupt date column value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Corrupt date column value

Author

Topic

MrMiagi
Starting Member

4 Posts

Posted - 2012-05-11 : 02:53:54

Hi

I'm running a SQL Server 2008 Express R2 database and recently one of the values in a date column on one of the tables have been corrupted.

When ever I try to select the corrupt value I get the following error:
An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

When I cast that column to an INT it returns the value 67149661 which is why I get the overflow error.

Now I am trying to either update or delete the record but when I try that I get yet another error:
Msg 8630, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor encountered an unexpected error during execution.

Is there any way that I can fix this value or at least remove it? I do have a backup with the original value.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-11 : 02:58:03

Run DBCC CHECKDB(DbNameGoesHere) to see if you've got data corruption.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

MrMiagi
Starting Member

4 Posts

Posted - 2012-05-11 : 03:11:22

Running the check returns the following error:

Msg 2570, Level 16, State 3, Line 1
Page (1:16731), slot 24 in object ID 213575799, index ID 1, partition ID 72057594096320512, alloc unit ID 72057594109034496 (type "In-row data"). Column "dtDate" value is out of range for data type "datetime". Update column to a legal value.

It says I have to update the value but that I can't do.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-11 : 08:45:21

Yes you can by using the primary key.

Is it a DATE or DATETIME column?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-11 : 08:49:34

Is the date from 2011-09-13 00:00:00.000 ?

N 56°04'39.26"
E 12°55'05.63"

MrMiagi
Starting Member

4 Posts

Posted - 2012-05-11 : 09:35:02

quote:
Originally posted by SwePeso

Yes you can by using the primary key.

Is it a DATE or DATETIME column?

N 56°04'39.26"
E 12°55'05.63"

Using the primary key give the same Internal Query error.

It is a DATETIME column.

MrMiagi
Starting Member

4 Posts

Posted - 2012-05-11 : 09:37:36

quote:
Originally posted by SwePeso

Is the date from 2011-09-13 00:00:00.000 ?

N 56°04'39.26"
E 12°55'05.63"

Actually it is that exact same date! How did you know that

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-11 : 10:06:22

Magic...

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-11 : 10:21:42

Adjust this sample code to your environment. The code below should preserve the time part too.

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		dt DATETIME
	)

-- Populate sample data
INSERT	@Sample
VALUES	(GETDATE())

-- Display the initial value
SELECT	*
FROM	@Sample

-- Start masking erraneous value
DECLARE	@OldValue BINARY(8)

-- Get the old value from sample table
SELECT	@OldValue = CAST(dt AS BINARY(8))
FROM	@Sample
WHERE	RowID = 1

-- Display the old binary value
SELECT	@OldValue

-- Mask the old value to get rid of out of range bits
SET	@OldValue = @OldValue & CAST(0x0000FFFFFFFFFFFF AS BIGINT)

-- Display new binary value
SELECT	@OldValue

-- Start updating the new value
DECLARE	@NewValue DATETIME

-- Convert the binary value to datetime
SET	@NewValue = CAST(@OldValue AS DATETIME)

-- Make the update
UPDATE	@Sample
SET	dt = @NewValue
WHERE	RowID = 1

-- Display the final value
SELECT	*
FROM	@Sample

Also see my blog post here for more information about the internal storage of datetime data type
http://weblogs.sqlteam.com/peterl/archive/2010/11/05/the-internal-storage-of-a-datetime-value.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources