'Not able to find out of range value for a table column of Date type

Executing the query DBCC CHECKDB WITH NO_INFOMSGS failed with the following error:

Page (1:2772455), slot 8 in object ID 689489585, index ID 1, partition ID 72057594305052672, alloc unit ID 72057594619953152 (type "In-row data"). Column "Date" value is out of range for data type "datetime".

Update column to a legal value.

I got above message while running DBCC checkDB command for a specific table of a database and when I tried to fetch the date column of that table by running below query:-

select [date] 
from  [TableName] 
where [Year] = 2011 
  and Month = 1 
  and [Date] < '1753-01-01'
  and Date > '9999-12-31'
sql-server


Solution 1:[1]

You can try using the Between keyword like this:-

  select [date] from  [TableName] where [Year]=2011 and Month=1 and [Date] between ('1753-01-01','9999-12-31')

Solution 2:[2]

First of all your WHERE clause does not make sense

  1. You are looking for Date which is earlier than '1753-01-01' and later than '9999-12-31' which does not exist.
  2. [Year] = 2011 means you need only 2011 records and no need to filter by a date range at all. For some reason if you need it should be as [date] BETWEEN '17530101' AND '99991231

  3. It is recommended to use ISO date format which is yyyymmdd (ie; '17530101'). This could be the reason for Date out of range error.

    I think this will do the job;

      SELECT [date] FROM  [TableName] WHERE [Year] = 2011 AND Month = 1 
  --AND [date] BETWEEN '17530101' AND '99991231' 
  --This filter does not make sense as you have already filtered by 2011.

Solution 3:[3]

You have the page (1:2772455) that contains the corrupted value, run this to find out exactly what row is causing the problems:

DBCC TRACEON ( 3604 )
DBCC PAGE ( 'dbname' , 1 , 2772455 , printopt=3 )
DBCC TRACEOFF ( 3604 )

Search for "INVALID" and you will find Date = INVALID COLUMN VALUE. Scroll up and get the primary key. You can then UPDATE the row with a correct Date.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Kaf
Solution 3 Sire

Related Questions