CODEWITHSUNDEEP
sql-servert-sql
user1452574
user1452574

Reputation: 485

Having strange date range error that only applies to certain date ranges

Okay, so this is a little hard to explain, basically the code below is comparing a ship date column vs 3 other date columns and then calculating an on time % for each. I'll explain the exact issue below the code:

SELECT
CAST(o.OrderNo AS INT) AS [OrderNo],
o.CustCode,
CASE
    WHEN d2.[Shipment Total] IS NULL THEN o.OrderTotal
    ELSE d2.[Shipment Total]
    END AS [OrderTotal],
CAST(SUBSTRING(o.NotesToCust, CHARINDEX('ISD',o.NotesToCust)+4, 8) AS DATE) AS [ISD],
CAST(o.User_Date1 AS DATE) AS [3 Week Ack Date],
CAST(o.User_Date2 AS DATE) AS [1 Week Ack Date],
CAST(d.ShipDate AS DATE) AS [Ship Date],
CASE
    WHEN CAST(d.Notes2Cust AS VARCHAR(MAX)) LIKE '%partial%' THEN 'Partial Shipment'
    WHEN CAST(d.Notes2Cust AS VARCHAR(MAX)) LIKE '%complete%' THEN 'Shipped Complete'
    END AS [Shipment Notes],
CAST(CASE
    WHEN o.User_Number3 <> '0'
    THEN o.User_Number3
    ELSE NULL
    END AS INT) AS [Late Reason Code(s)],
(CAST(SUM(CASE
    WHEN CAST(d.ShipDate AS DATE) <= CAST(SUBSTRING(o.NotesToCust, CHARINDEX('ISD',o.NotesToCust)+4, 8) AS DATE)
    AND CAST(d.Notes2Cust AS VARCHAR(MAX)) NOT LIKE '%partial%'
    AND (CAST(o.User_Number3 AS INT) IS NULL
    OR CAST(o.User_Number3 AS INT) = 0
    OR CAST(o.User_Number3 AS INT) = 3
    OR CAST(o.User_Number3 AS INT) = 4
    OR CAST(o.User_Number3 AS INT) = 5
    OR CAST(o.User_Number3 AS INT) = 6
    OR CAST(o.User_Number3 AS INT) = 7
    OR CAST(o.User_Number3 AS INT) = 8
    OR CAST(o.User_Number3 AS INT) = 9)
    AND o.CustCode NOT LIKE '%Euroline%'
    AND o.CustCode NOT LIKE 'Savi B%'
    THEN 1
    ELSE 0
    END) OVER(PARTITION BY MONTH(d.ShipDate)) AS FLOAT))/
        NULLIF((CAST(SUM(CASE
            WHEN CAST(d.Notes2Cust AS VARCHAR(MAX)) NOT LIKE '%partial%'
            AND (CAST(o.User_Number3 AS INT) IS NULL
            OR CAST(o.User_Number3 AS INT) = 0
            OR CAST(o.User_Number3 AS INT) = 3
            OR CAST(o.User_Number3 AS INT) = 4
            OR CAST(o.User_Number3 AS INT) = 5
            OR CAST(o.User_Number3 AS INT) = 6
            OR CAST(o.User_Number3 AS INT) = 7
            OR CAST(o.User_Number3 AS INT) = 8
            OR CAST(o.User_Number3 AS INT) = 9)
            AND o.CustCode NOT LIKE '%Euroline%'
            AND o.CustCode NOT LIKE 'Savi B%'
            THEN 1
            ELSE 0
            END) OVER(PARTITION BY MONTH(d.ShipDate)) AS FLOAT)),0) AS [ISD On-Time %],
(CAST(SUM(CASE
    WHEN CAST(d.ShipDate AS DATE) <= CAST(o.User_Date1 AS DATE)
    AND CAST(d.Notes2Cust AS VARCHAR(MAX)) NOT LIKE '%partial%'
    AND (CAST(o.User_Number3 AS INT) IS NULL
    OR CAST(o.User_Number3 AS INT) = 0
    OR CAST(o.User_Number3 AS INT) = 3
    OR CAST(o.User_Number3 AS INT) = 4
    OR CAST(o.User_Number3 AS INT) = 5
    OR CAST(o.User_Number3 AS INT) = 6
    OR CAST(o.User_Number3 AS INT) = 7
    OR CAST(o.User_Number3 AS INT) = 8
    OR CAST(o.User_Number3 AS INT) = 9)
    AND o.CustCode NOT LIKE '%Euroline%'
    AND o.CustCode NOT LIKE 'Savi B%'
    THEN 1
    ELSE 0
    END) OVER(PARTITION BY MONTH(d.ShipDate)) AS FLOAT))/
        NULLIF((CAST(SUM(CASE
            WHEN CAST(d.Notes2Cust AS VARCHAR(MAX)) NOT LIKE '%partial%'
            AND (CAST(o.User_Number3 AS INT) IS NULL
            OR CAST(o.User_Number3 AS INT) = 0
            OR CAST(o.User_Number3 AS INT) = 3
            OR CAST(o.User_Number3 AS INT) = 4
            OR CAST(o.User_Number3 AS INT) = 5
            OR CAST(o.User_Number3 AS INT) = 6
            OR CAST(o.User_Number3 AS INT) = 7
            OR CAST(o.User_Number3 AS INT) = 8
            OR CAST(o.User_Number3 AS INT) = 9)
            AND o.CustCode NOT LIKE '%Euroline%'
            AND o.CustCode NOT LIKE 'Savi B%'
            THEN 1
            ELSE 0
            END) OVER(PARTITION BY MONTH(d.ShipDate)) AS FLOAT)),0) AS [3 Week On-Time %],
(CAST(SUM(CASE
    WHEN CAST(d.ShipDate AS DATE) <= CAST(o.User_Date2 AS DATE)
    AND CAST(d.Notes2Cust AS VARCHAR(MAX)) NOT LIKE '%partial%'
    AND (CAST(o.User_Number3 AS INT) IS NULL
    OR CAST(o.User_Number3 AS INT) = 0
    OR CAST(o.User_Number3 AS INT) = 3
    OR CAST(o.User_Number3 AS INT) = 4
    OR CAST(o.User_Number3 AS INT) = 5
    OR CAST(o.User_Number3 AS INT) = 6
    OR CAST(o.User_Number3 AS INT) = 7
    OR CAST(o.User_Number3 AS INT) = 8
    OR CAST(o.User_Number3 AS INT) = 9)
    AND o.CustCode NOT LIKE '%Euroline%'
    AND o.CustCode NOT LIKE 'Savi B%'
    THEN 1
    ELSE 0
    END) OVER(PARTITION BY MONTH(d.ShipDate)) AS FLOAT))/
        NULLIF((CAST(SUM(CASE
            WHEN CAST(d.Notes2Cust AS VARCHAR(MAX)) NOT LIKE '%partial%'
            AND (CAST(o.User_Number3 AS INT) IS NULL
            OR CAST(o.User_Number3 AS INT) = 0
            OR CAST(o.User_Number3 AS INT) = 3
            OR CAST(o.User_Number3 AS INT) = 4
            OR CAST(o.User_Number3 AS INT) = 5
            OR CAST(o.User_Number3 AS INT) = 6
            OR CAST(o.User_Number3 AS INT) = 7
            OR CAST(o.User_Number3 AS INT) = 8
            OR CAST(o.User_Number3 AS INT) = 9)
            AND o.CustCode NOT LIKE '%Euroline%'
            AND o.CustCode NOT LIKE 'Savi B%'
            THEN 1
            ELSE 0
            END) OVER(PARTITION BY MONTH(d.ShipDate)) AS FLOAT)),0) AS [1 Week On-Time %]
FROM Orders o 
JOIN OrderDet od ON o.OrderNo = od.OrderNo
JOIN TimeTicketDet t ON od.JobNo = t.JobNo
LEFT JOIN DelTicket d ON o.OrderNo = d.OrderNo AND d.Notes2Cust NOT LIKE '%rework%'
LEFT JOIN
    (
    SELECT
        od1.OrderNo AS [OrderNo],
        d1.ShipDate AS [ShipDate],
        d1.DelTicketNo AS [DelTicketNo],
        SUM(od1.UnitPrice*od1.QtyOrdered) AS [Shipment Total]
    FROM OrderDet od1
        JOIN DelTicketDet dt1 ON od1.JobNo = dt1.JobNo
        JOIN DelTicket d1 ON dt1.DelTicketNo = d1.DelTicketNo
    GROUP BY od1.OrderNo, d1.ShipDate, d1.DelTicketNo
    ) AS [d2] ON d.OrderNo = d2.OrderNo AND d.ShipDate = d2.ShipDate
WHERE CONVERT(VARCHAR(MAX), o.User_Memo1) IS NOT NULL
    AND SUBSTRING(o.User_Memo1, 1, 3) = 'Cut'
    AND CAST(d.ShipDate AS DATE) BETWEEN '20180101' AND '20180331'
    AND o.CustCode NOT LIKE '%Euroline%'
    AND o.CustCode NOT LIKE 'Savi B%'
GROUP BY 
o.OrderNo, o.CustCode, d2.[Shipment Total], o.OrderTotal, CAST(SUBSTRING(o.NotesToCust, CHARINDEX('ISD',o.NotesToCust)+4, 8) AS DATE), CAST(o.User_Date1 AS DATE), CAST(o.User_Date2 AS DATE),
CAST(d.ShipDate AS DATE), d.ShipDate, o.User_Number3, CAST(d.Notes2Cust AS VARCHAR(MAX))
ORDER BY CAST(d.ShipDate AS DATE) DESC

Okay, so this query returns the following error: "Conversion failed when converting date and or time from character string"

So okay, I then tried changing this line:

AND CAST(d.ShipDate AS DATE) BETWEEN '20180101' AND '20180331'

To be in between 20180101 and 20180228 and it works!! Okay so then I figured that the conversion issue is with something that I shipped between March 1-31. So then I changed that same line to say in between 20180301 and 20180331, which should not work, however, it does, and this is what is causing me a headache. Essentially, if I were to union the same code with one block saying from 20180101 to 20180228 and the second block to capture the March records, it would work just fine. However, if I go between Jan 1 and Mar 31, it doesn't work. What the heck??

I just checked every field that is being converted and there shouldn't be any errors, everything that I am converting to a date should 100% work. I'm really at a loss at how to proceed here. Union is not really an option, because I'd need like 25 unions to capture all the data I need over the span of a couple years. Besides, it just doesn't make sense to have to use that as a workaround when the code should execute just fine

Upvotes: 0

Views: 47

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

There isn't enough information to give you a definitive answer, but here is how you can find it.

The error occurs because you try to cast a string to a date and the string isn't a valid date. The snippet CAST(SUBSTRING(o.NotesToCust, CHARINDEX('ISD',o.NotesToCust)+4, 8) AS DATE) is obviously casting a string to a date, but I don't know where else in your query it might be happening, because I don't know which of your columns are strings, and which are date/datetime.

But what you can do is, everywhere in your query where you cast a varchar to a date, you can change the cast to TRY_CAST(), and look for the rows where TRY_CAST() resulted in a NULL being returned. Then you'll find the rows that are causing the error.

Upvotes: 2

Related Questions