473,216 Members | 2,232 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Error : Difference of two datetime columns caused overflow at runtime.

At my job is a dts package that is failing in SQL 2005. I am not a SQL
expert. I am just trying to fix. I put the query in Query Analyzer
and get this error:
(4322 row(s) affected)

Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.


I am just trying to understand what this means, what I should be
looking for and what could be wrong. Here is the query:
SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,
1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,
1970',
s.Warranty_Enddate) AS Support_EndDt,
DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) AS
Registration_Date, c.FirstName AS enduser_fname,
c.LastName AS enduser_lname, c.CompanyName AS
enduser_companyname, c.ContactEmail AS enduser_email, c.Address AS
enduser_address1,
c.Address2 AS enduser_address2, c.City AS
enduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,
c.WorkPhone AS enduser_phone,
c.Fax AS enduser_fax, d.DealerName AS
dealer_companyname, d.ContactFirstName AS dealer_fname,
d.ContactLastName AS dealer_name,
d.Address1 AS dealer_address, d.City AS
dealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,
d.ContactPhone AS dealer_phone,
d.ContactFax AS dealer_fax,
ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknown
IWP product') AS product_type, '' AS extra1,
'' AS extra2, '' AS extra3, '' AS extra4, '' AS
extra5, '' AS extra6, '' AS extra7
FROM tblInventory i full outer JOIN
tblDealers d ON i.DealerID = d.DealerID full
OUTER JOIN
tblSupport s ON i.InventoryID = s.InventoryID
full outer JOIN
tblCustomers c ON s.InventoryID = c.InventoryID
LEFT OUTER JOIN
tblProducts p ON LEFT(i.SerialNumber,
PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefix
WHERE i.SerialNumber <> ''
Any ideas would be greatly appreciated.

Sep 23 '05 #1
2 19109
ge*******@gmail.com (ge*******@gmail.com) writes:
At my job is a dts package that is failing in SQL 2005. I am not a SQL
expert. I am just trying to fix. I put the query in Query Analyzer
and get this error:
(4322 row(s) affected)

Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.
I am just trying to understand what this means, what I should be
looking for and what could be wrong. Here is the query:
SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,
1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,
1970',
s.Warranty_Enddate) AS Support_EndDt,
DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) AS


One or of the rows has a value that is in 2038 or later in one of the
columns. My bets are on Warranty_Enddate and somehas put in 99991231
for an infinite warranty.

(2038-01-19 03:14:07.000 is the time when the number of seconds since
1970-01-01 exceeds the range of an un integer.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 23 '05 #2
>From Transact-SQL Reference:

DATEDIFF produces an error if the result is out of range for integer
values. For milliseconds, the maximum number is 24 days, 20 hours, 31
minutes and 23.647 seconds. For seconds, the maximum number is 68 years.

Sep 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Codemonkey | last post by:
Hi, When I first installed Visual Studio 2003, I noticed I was getting the follow error when showing a form: A first chance exception of type 'System.ArithmeticException' occurred in...
0
by: geekwagon | last post by:
At my job is a dts package that is failing in SQL 2005. I am not a SQL expert. I am just trying to fix. I put the query in Query Analyzer and get this error: (4322 row(s) affected) ...
4
by: Saso Zagoranski | last post by:
Hi! I have created an SqlCommand object and I have set some parameters to it... One of the parameters is of the DateTime type; here is the code: sqlCommand.Parameters.Value =...
6
by: rn5a | last post by:
During registration, users are supposed to enter the following details: First Name, Last Name, EMail, UserName, Password, Confirm Password, Address, City, State, Country, Zip & Phone Number. I am...
3
by: MLH | last post by:
A wide comma delimited text file import into A97 failed with an overflow error. I'm estimating it to have been no more than 1000 records. 100% of all the fields were quoted strings or ZLS's. ...
3
by: jer006 | last post by:
Hi I am writing a select statement that has an arithmetic function inside a case statement that uses logic to decide whether to divide or multiply and when I run the arithmetic statements outside...
3
by: bbawa1 | last post by:
Hi, I have a table which has a field ItemsReceived of type datetime. I have a grid view which has two columns. In first column i have to show the data from field ItemsReceived and in second...
7
by: LucasLondon | last post by:
Hi, I have the code below that I adapted to loop through each column of data (Columns A to GR) to perform an a calculation based on the data in rows 2 to 31 of each column and place the...
13
by: lucky13 | last post by:
Dear All, I m connecting my sql data with the data grid, but not all columns only particular columns. having certain conditions, but it is showing the error as Runtime Error '6- overflow... ...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.