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

Improve this question
3
  • possible duplicate of Getting SqlDateTime overflow. exception in 2008 and stackoverflow.com/q/1226863 Commented Aug 13, 2014 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' Commented Aug 13, 2014 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. Commented Aug 13, 2014 at 13:17

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

Sorry guys blonde moment, is there a way I can get these records changes, and what is the easiest why to do this?
What do you want to change?
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?
please add some sample data to the post and I'll take a look
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
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.