current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

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

share|improve this question
    
possible duplicate of Getting SqlDateTime overflow. exception in 2008 and 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' –  DMason 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. –  chipmunkofdoom2 Aug 13 '14 at 13:17
    
look up ISDATE() –  KM. Aug 13 '14 at 13:43

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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|Reque‌​stDateTime|RequestComments|IsPrinted|QueueIdentifier|Created|CreateByUserId|Final‌​isationDateTime|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.