Posted by geekwagon@gmail.com on 09/29/13 11:27

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.

[Back to original message]