September 21, 2012 at 4:16 am
Hi Guys,
The following query results in the error "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value."
If I remove the isdate from the where clause I can run the query but unable to identify the problem record, any idea how I could go about doing this?
All records are between 1753 and 9999?
Thanks,
Rich
Select U.START_DATE
FROM CSMADM.dbo.USAGES U
INNER JOIN CSMAUAT_MSCRM.dbo.Account A
on CONVERT(VARCHAR(12),U.MEM_MEMBER_NO) = A.AccountNumber
INNER JOIN CSMAUAT_MSCRM.dbo.Product P
on convert(varchar(10),U.SPT_PRO_PRODUCT_CODE) = P.ProductNumber
WHERE (P.swmship_ProductGroup =200000003 or P.swmship_ProductGroup = 796710000)
AND
U.SPT_PRO_PRODUCT_CODE
NOT IN
(44036,42928,42075,42076,44394,42922,41901,43954)
and u.ROWID NOT IN
(Select [swptn_productusageId] From [CSMAUAT_MSCRM].[dbo].[swptn_productusageExtensionBase])
and ISDATE(CAST(u.start_date as datetime)) = 0
order by U.START_DATE
September 21, 2012 at 4:36 am
What is the output of the below
SELECT
DISTINCT
YEAR(U.START_DATE)
FROM
CSMADM.dbo.USAGES U
September 21, 2012 at 5:02 am
This contains years prior to 1753 but these are removed once I add the inner joins into the query.
SELECT
DISTINCT
YEAR(U.START_DATE)
FROM
CSMADM.dbo.USAGES U
INNER JOIN CSMAUAT_MSCRM.dbo.Account A
on CONVERT(VARCHAR(12),U.MEM_MEMBER_NO) = A.AccountNumber
INNER JOIN CSMAUAT_MSCRM.dbo.Product P
on convert(varchar(10),U.SPT_PRO_PRODUCT_CODE) = P.ProductNumber
order by 1
September 21, 2012 at 5:06 am
What are the two values returned by this
SELECT
MIN(U.START_DATE),
MAX(U.START_DATE)
FROM
CSMADM.dbo.USAGES U
INNER JOIN CSMAUAT_MSCRM.dbo.Account A
on CONVERT(VARCHAR(12),U.MEM_MEMBER_NO) = A.AccountNumber
INNER JOIN CSMAUAT_MSCRM.dbo.Product P
on convert(varchar(10),U.SPT_PRO_PRODUCT_CODE) = P.ProductNumber
September 21, 2012 at 5:24 am
Thanks Anthony, it would seem that even though the inner joins removed the years prior to 1753 somehow this records still required processing so caused the conversion error. I have resolved this by using a sub query where the year is > 1753 instead of selecting the entire usages table.
Thanks again.
Rich
September 21, 2012 at 5:29 am
If the year was 1753 it should process fine, which is why I wanted to know the min and max date from the result set as it would seem its leaving some dates in which are outside the scope of datetime.
September 21, 2012 at 6:14 am
When the Inner Joins are used all dates are post 1753 (Min 1940 Max 2020) but as the table contains values pre 1753 it still caused an error, I didn't think this would be the case but changing the from to a sub query has resolved the problem.
September 21, 2012 at 6:20 am
Logical query processing order "should" of removed all the invalid dates as the joins are processed before the where clause, so if the joins did remove all dates which cannot be converted in "theory" the conversion shouldn't have failed.
But if adding the additional clause in the where solves the issue, it works for me.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply