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_Endd ate) AS Support_EndDt,
DATEDIFF([second], 'Jan 1, 1970', c.Registration_ Date) AS
Registration_Da te, c.FirstName AS enduser_fname,
c.LastName AS enduser_lname, c.CompanyName AS
enduser_company name, c.ContactEmail AS enduser_email, c.Address AS
enduser_address 1,
c.Address2 AS enduser_address 2, 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_companyn ame, d.ContactFirstN ame AS dealer_fname,
d.ContactLastNa me 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(SUBSTRIN G(p.ProductName , 11, LEN(p.ProductNa me) - 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.SerialNu mber,
PATINDEX('%-%', i.SerialNumber) ) = p.SerialPrefix
WHERE i.SerialNumber <> ''
Any ideas would be greatly appreciated.
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_Endd ate) AS Support_EndDt,
DATEDIFF([second], 'Jan 1, 1970', c.Registration_ Date) AS
Registration_Da te, c.FirstName AS enduser_fname,
c.LastName AS enduser_lname, c.CompanyName AS
enduser_company name, c.ContactEmail AS enduser_email, c.Address AS
enduser_address 1,
c.Address2 AS enduser_address 2, 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_companyn ame, d.ContactFirstN ame AS dealer_fname,
d.ContactLastNa me 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(SUBSTRIN G(p.ProductName , 11, LEN(p.ProductNa me) - 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.SerialNu mber,
PATINDEX('%-%', i.SerialNumber) ) = p.SerialPrefix
WHERE i.SerialNumber <> ''
Any ideas would be greatly appreciated.