March 31, 2009 at 7:48 am
When I trying to fetch the data from One table, That show only 400 records
Then It dropped and say the 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.
"
It has 53 column and Five column like Datetime..
What i do?
But That table having More then 2lacks Records..
Please help me
Thanks In advance
///
March 31, 2009 at 7:54 am
Since you haven't provided us with the DDL (CREATE TABLE statement) for the table, the code you are running, or sample data, its hard to say for sure. I have a guess that you have dates stored as character strings and when converted to a datetime value, it falls outside the allowed range of dates.
March 31, 2009 at 8:07 am
you just have to filter out those bad records(The ones whose date range does not lies between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM)
and import the good ones, or fix those bad records. Use some SELECT with WHERE clause to get bad records.
June 11, 2009 at 2:00 pm
We get this error when we select * on one of our records in one table (using like where id = 12345). I can see all the dates and nulls in the date columns. So there is nothing wrong with the values in those date fields.
DBCC on table and database give no errors.
I am not sure where to look next. Maybe it is corruption on a non-date field.
----------------------------------------------------
June 11, 2009 at 2:05 pm
What is the data type of the field you are using in the WHERE clause, and what exactly is the code you are using?
Likely, as Lynn mentioned, you have an implicit conversion taking place, not corruption or a "problem? with the fields.
June 11, 2009 at 2:16 pm
mmartin (6/11/2009)
Maybe it is corruption on a non-date field.
If it was corruption the error would not say 'date time conversion'. It would say something like this "SQL Server detected a logical consistency-based I/O error: "
You've most likely got, as the error says, a conversion problem.
Post the query and the table definition and we can possibly help you further.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 3:38 pm
I run select * from accounts where id = 509416 and the SSMS just hangs there executing the query - even stopping doesn't help, I have to close the tab. It does not do this for other ID's as far as I'm aware. The id field is of type INT.
Consider the following:
SELECT *
FROM [dbo].[Accounts]
where id > 509415
Order by id ,[filedate] Option(MaxDop 1)
Sometimes this above works, but the results leaves out id 509416. Other times I get the 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.
.
The table has just over 140 columns (it is not normalised, designed before I got here). I see no message about conversion here.
Mark
----------------------------------------------------
June 11, 2009 at 3:42 pm
I should be a bit more clear:
I can query all the date fields in the record mentioned (id 509416). They show up as a date or as NULL. But the select * on that account causes a hang up.
----------------------------------------------------
June 11, 2009 at 3:49 pm
You said you have 5 datetime columns and about 400 results in that table right? If thats the case, is it possible for you to copy the result set of those 5 datetime column and result set in an excel or notepad and attach here as an attachment?
June 11, 2009 at 4:00 pm
Hi Kal -- I'm not the original author of this thread. I am just issuing my own grievances with the same message the author gets. All of our date columns are of the "datetime" type.
Basically my situation is if I query all the columns for that one specific ID then management studio hangs. If I go one ID below (select * from accounts where id > 509415) then this produces the error message indicated.
If I select only the "datetime" fields I have no issues. I find it quite enigmatic.
----------------------------------------------------
June 11, 2009 at 4:37 pm
I did something like the following
set rowcount 0
Select id,
case when datecolumn1 is null then 2 else isdate(datecolumn1) end as c1, /*isdate returns 1 when true and 0 when false*/
case when datecolumn2 is null then 2 else isdate(datecolumn2) end as c2,
case when datecolumn3 is null then 2 else isdate(datecolumn3) end as c3
into #myTempTable
FROM accounts where id > 509415
Then I looked for zeroes which implies isdate found some non-date values (& not null obviously).
select * from #myTempTable
WHERE (c1=0 or c2=0 or c3=0)
This yielded nothing, which leads me to think there are no funny characters in there. Thus to re-iterate I dont think the date fields are causing the problem.
----------------------------------------------------
June 12, 2009 at 5:37 am
Is "accounts" a table or a view, and does it have any computed columns in it?
June 12, 2009 at 8:11 am
You might have Date that is out of range of 'Datetime data type' dates i.e. Less than January 1 1753 or greater than Dec 31 9999. If your date is out of range of these dates, then u can still gat the error ur getting. As an E.G.
DECLARE @datestring varchar(8)
SET @datestring = '12/21/1098'
SELECT ISDATE(@datestring)
Still prints out 1, because its an valid date but still out of range of DATETIME data type making it invalid date.
June 12, 2009 at 10:17 am
Hi and thanks for your replies --
Accounts is a table, thus there are no calculations occurring.
I will post any news I have for I am working away on isolating the field(s) causing the issue.
And Just FYI, all the date fields are of DATETIME, no small date times or any thing else.
----------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply