1

I have a SQL database, that seems to be corrupt. Error message below

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.

I ran the below script, now when I get the result in the table and I search for those records they are not there, but yet they found them?

SELECT RequestDateTime 
FROM dbo.PickingSlip 
WHERE RequestDateTime < '1/1/1753 12:00:00 AM' 
   OR RequestDateTime > '12/31/9999 11:59:59 PM'

This is some of the data in the table.

Id|RequestedDataFileId|CurrentShelf|PickingSlipStatusId|ByUserId|ForUserId|RequestDateTime|RequestComments|IsPrinted|QueueIdentifier|Created|CreateByUserId|FinalisationDateTime|FinalisationByUserId|ConfigAccountId 10067|5356276|0007a|43|8|6|2013-04-23 11:22:32.010||1|NULL|2013-04-23 11:22:32.010|8|2013-04-23 11:23:07.577|8|5 10068|5356547|0005A|43|8|6|2013-04-25 11:10:51.027||1|NULL|2013-04-25 11:10:51.027|8|2013-04-25 11:11:20.497|8|5

user3906930
  • 67
  • 2
  • 10
  • possible duplicate of [Getting SqlDateTime overflow. exception in 2008](http://stackoverflow.com/questions/5977492/getting-sqldatetime-overflow-exception-in-2008) and http://stackoverflow.com/q/1226863 – Ken White Aug 13 '14 at 13:15
  • I think you have your greater than/less than operators reversed in your query. Try `WHERE RequestDateTime > '1/1/1753 12:00:00 AM' OR RequestDateTime < '12/31/9999 11:59:59 PM'` – Dave Mason Aug 13 '14 at 13:15
  • This error message is saying that the date you're trying to add to the table, update in the table, or calculate, is outside of the valid date range. It's not saying that you have an invalid date in the table. I'd advise you check your source file or transformation logic to make sure the dates you're trying to put in the table or update in the table never go outside that range. – Patrick Tucci Aug 13 '14 at 13:17

1 Answers1

2

As @DMason says in the comments, your comparison operators are the wrong way round.

So if you try this instead, you should get results:

SELECT RequestDateTime 
FROM dbo.PickingSlip 
WHERE RequestDateTime >= '1/1/1753 12:00:00 AM' 
   OR RequestDateTime <= '12/31/9999 11:59:59 PM'

In your original query you state RequestDateTime < '1/1/1753 12:00:00 AM', is looking for dates smaller than the minimum date.

Similarly, RequestDateTime > '12/31/9999 11:59:59 PM' is looking for dates greater than the maximum, which is what the error is telling you.

If you want to filter dates you can format dates into ISO format (YYYY-MM-DD), so to find all requests in 2013, you would do:

SELECT RequestDateTime 
FROM dbo.PickingSlip 
WHERE RequestDateTime >= '2013-01-01' 
   OR RequestDateTime <= '2013-12-31'

Or you can use the between syntax:

SELECT RequestDateTime 
FROM dbo.PickingSlip 
WHERE RequestDateTime BETWEEN '2014-01-01' AND '2014-12-31'

Valid Dates

To check for valid dates, you can use ISDATE() to check for invalid dates and return the rows:

SELECT ID, RequestDateTime
FROM PickingSlip
WHERE ISDATE(RequestDateTime) = 0

Simply change it to WHERE ISDATE(RequestDateTime) = 1 to return the valid dates.

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • Sorry guys blonde moment, is there a way I can get these records changes, and what is the easiest why to do this? – user3906930 Aug 14 '14 at 11:28
  • What do you want to change? – Tanner Aug 14 '14 at 12:30
  • I want the date format to be in-line with the database format, I want to be able to view all the records and not only the ones that are correct. I hope I'm making sense? – user3906930 Aug 14 '14 at 12:44
  • please add some sample data to the post and I'll take a look – Tanner Aug 14 '14 at 12:48
  • Id|RequestedDataFileId|CurrentShelf|PickingSlipStatusId|ByUserId|ForUserId|RequestDateTime|RequestComments|IsPrinted|QueueIdentifier|Created|CreateByUserId|FinalisationDateTime|FinalisationByUserId|ConfigAccountId 10067|5356276|0007a|43|8|6|2013-04-23 11:22:32.010||1|NULL|2013-04-23 11:22:32.010|8|2013-04-23 11:23:07.577|8|5 10068|5356547|0005A|43|8|6|2013-04-25 11:10:51.027||1|NULL|2013-04-25 11:10:51.027|8|2013-04-25 11:11:20.497|8|5 – user3906930 Aug 14 '14 at 12:59
  • this is the columns - **id|RequestedDataFileId|CurrentShelf|PickingSlipStatusId|ByUserId|ForUserId|Reque‌​stDateTime|RequestComments|IsPrinted|QueueIdentifier|Created|CreateByUserId|Final‌​isationDateTime|FinalisationByUserId|ConfigAccountId** – user3906930 Aug 14 '14 at 13:06
  • This is the date - **10067|5356276|0007a|43|8|6|2013-04-23 11:22:32.010||1|NULL|2013-04-23 11:22:32.010|8|2013-04-23 11:23:07.577|8|5 10068|5356547|0005A|43|8|6|2013-04-25 11:10:51.027||1|NULL|2013-04-25 11:10:51.027|8|2013-04-25 11:11:20.497|8|5** – user3906930 Aug 14 '14 at 13:07
  • can you add it to your question please, as it's related to that, and anyone else that looks at this post will find it more useful there than in comments – Tanner Aug 14 '14 at 13:12
  • Even still there are too many columns, your query only looks at one column, so the sample data should just be a single column with multiple rows of sample values for `RequestDateTime` – Tanner Aug 14 '14 at 13:22
  • I know my query only looks at one column, it was the only way I know how tho check for the errors. if there is a better may, please teach me. I would appreciate it. – user3906930 Aug 14 '14 at 13:37
  • I don't fully understand what you are asking but I've updated my answer to show some methods of filtering results by date. – Tanner Aug 14 '14 at 13:45
  • I'm trying to see how I can fix the record in the database, Because its not showing all records, and every time i run a script it gives me the first error I spoke about, then I tried to look for these errors so I could some how fix, update, change these records so that the database can recognize the records again. apologies for not making sense, but its the best way i know how to explain?? – user3906930 Aug 14 '14 at 13:56
  • thank for the script. I ran it it gave me the following message with no result. Msg 542, Level 16, State 1, Line 1 An invalid datetime value was encountered. Value exceeds the year 9999. – user3906930 Aug 14 '14 at 14:30
  • I manage to find a solution `DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS` – user3906930 Aug 15 '14 at 12:05