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.

geekwagon@gmail.com (geekwagon@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, esquel@sommarskog.se

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

>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.