need your expertise on the "Conversion failed when converting date and/or time from character string". Much written about it and tried looking though it and understanding it to the extend I could but it did not solve the issue.
My code includes a number on conversations from dates and strings to the common numerical format. The conversation work separately form the main code and even together when the code is loose. However, when I start adding more conditions in the "where" function, SQL doesn't seem to like it. Do you possible know how to handle it best? Any help is much appreciated.
SELECT DISTINCT
DOTWHL as "DC",
DORELD as "Release date",
DODLDT as "Prelim ETA",
DOITNO as "Item #",
MMITDS as "Item name",
MMSTCN as "Lkr",
DOPPQT as "Plan Qty",
DOPPQT/MMCFI2 as "Plan Qty, pall",
DOPSTS as "Sts",
SUM(MHFOQT) as "FC between ETA & BBD",
MLSTQT as "Stock, LHU",
MMCFI2 as "LHU/PAL",
MMCFI3 as "LHU/Layer",
CASE WHEN DOPPQT >= MLSTQT
THEN CASE
WHEN MLSTQT > SUM(MHFOQT)
THEN 'YES'
ELSE 'NO'
END
ELSE CASE
WHEN DOPPQT > SUM(MHFOQT)
THEN 'YES'
ELSE 'NO'
END
END AS "FEFO violation necessary?",
CASE WHEN DOPPQT >= MLSTQT
THEN CASE
WHEN MLSTQT > SUM(MHFOQT)
THEN SUM(MHFOQT)
ELSE MLSTQT
END
ELSE CASE
WHEN DOPPQT > SUM(MHFOQT)
THEN SUM(MHFOQT)
ELSE DOPPQT
END
END AS "Recommended Qty, LHU",
CASE WHEN DOPPQT >= MLSTQT
THEN CASE
WHEN MLSTQT > SUM(MHFOQT)
THEN SUM(MHFOQT)/MMCFI2
ELSE MLSTQT/MMCFI2
END
ELSE CASE
WHEN DOPPQT > SUM(MHFOQT)
THEN SUM(MHFOQT)/MMCFI2
ELSE DOPPQT/MMCFI2
END
END AS "Recommended Qty, pall",
MLBREF as "Batch",
CASE WHEN MLSTQT > DOPPQT
THEN CASE
WHEN DOPPQT < SUM(MHFOQT)
THEN SUM(MHFOQT)-DOPPQT
ELSE NULL
END
ELSE NULL
END AS "Fill Up possibility, LHU",
Sales.Sold_Qty as "Sold Qty 10 weeks",
Sales.Forecasted_Qty as "FC Qty 10 weeks",
ISNULL(Sales.Sold_Qty/NULLIF(Sales.Forecasted_Qty,0), 0) as "Sold/FC, %",
MBBUYE as "NP",
MMRESP as "DmP",
CASE
WHEN Other_Assortments.item <> 0 THEN 'Many Markets'
ELSE 'DC 091 only'
END as "Distributed on"
FROM MVXJDTA.MDOPLP
LEFT JOIN MVXJDTA.MITMAS
ON DOCONO=MMCONO AND DOITNO=MMITNO
LEFT JOIN MVXJDTA.MITBAL
ON DOCONO=MBCONO AND DOFWHL=MBWHLO AND DOITNO=MBITNO
LEFT JOIN MVXJDTA.MITSTA
ON DOCONO=MHCONO AND DOTWHL=MHWHLO AND DOITNO=MHITNO
LEFT JOIN MVXJDTA.MITLOC
ON DOCONO=MLCONO AND DOFWHL=MLWHLO AND DOITNO=MLITNO
LEFT JOIN
(
SELECT DISTINCT
MHITNO as "Item_#",
sum(MHSOQT) as "Sold_Qty",
sum(MHFOQT) as "Forecasted_Qty"
FROM MVXJDTA.MITSTA
WHERE MHCONO=1
AND MHWHLO='091'
AND MHCYP6 between
(cast(datepart(yyyy, dateadd(ww,-10,getdate())) as varchar) + right('00' + cast(datepart(ww, dateadd(ww,-10,getdate())) as varchar),2)) and
(cast(datepart(yyyy, dateadd(ww,-1,getdate())) as varchar) + right('00' + cast(datepart(ww, dateadd(ww,-1,getdate())) as varchar),2))
AND right(MHCYP6, 2) <>'00'
group by MHITNO
)
as Sales
on DOITNO=Sales.Item_#
LEFT JOIN
(
select distinct
OIITNO as "item"
from MVXJDTA.OCUSMA
left join MVXJDTA.OASCUS
on OKCONO=OCCONO and OKCUNO=OCCUNO
left join MVXJDTA.OASITN
on OKCONO=OICONO and OCASCD=OIASCD
where OKCONO=1
and OITDAT='99999999'
and OKCSCD<>'KR'
and OKCSCD<>'HK'
and OKCSCD<>'TW'
and OKCSCD<>'SG'
and OKCSCD<>'MY'
and OKCSCD<>'TH'
and OKCSCD<>'PH'
group by OIITNO) as Other_Assortments
on Other_Assortments.item=DOITNO
WHERE DOCONO=1
AND DOTWHL='091'
AND DOFWHL='010'
AND MHCYP6 NOT IN (
SELECT
MHCYP6
FROM MVXJDTA.MITSTA
WHERE MHWHLO='091'
AND MHCONO=1
AND RIGHT(CONVERT(VARCHAR, MHCYP6, 102), 2) ='00'
)
AND MHCYP6 BETWEEN
CASE
WHEN DATEPART(WEEK, CONVERT(DATETIME, RIGHT(DODLDT, 2) + '/' + LEFT(RIGHT(DODLDT, 4), 2) + '/' + LEFT (DODLDT, 4), 103))<10
THEN CONCAT( DATEPART(YEAR, CONVERT(DATETIME, RIGHT(DODLDT, 2) + '/' + LEFT(RIGHT(DODLDT, 4), 2) + '/' + LEFT (DODLDT, 4), 103)), 0,
DATEPART(WEEK, CONVERT(DATETIME, RIGHT(DODLDT, 2) + '/' + LEFT(RIGHT(DODLDT, 4), 2) + '/' + LEFT (DODLDT, 4), 103)))
ELSE CONCAT( DATEPART(YEAR, CONVERT(DATETIME, RIGHT(DODLDT, 2) + '/' + LEFT(RIGHT(DODLDT, 4), 2) + '/' + LEFT (DODLDT, 4), 103)),
DATEPART(WEEK, CONVERT(DATETIME, RIGHT(DODLDT, 2) + '/' + LEFT(RIGHT(DODLDT, 4), 2) + '/' + LEFT (DODLDT, 4), 103)))
END
CASE
WHEN MLBREF IS NULL
THEN '999999'
ELSE CASE
WHEN DATEPART(WEEK, DATEADD(DAY, -82, CONVERT(DATETIME, RIGHT(MLBREF, 2) + '/' + LEFT(RIGHT(MLBREF, 4), 2) + '/' + '20'+LEFT (MLBREF, 2), 103)))<10
THEN CONCAT(DATEPART(YEAR, DATEADD(DAY, -82, CONVERT(DATETIME, RIGHT(MLBREF, 2) + '/' + LEFT(RIGHT(MLBREF, 4), 2) + '/' + '20'+LEFT (MLBREF, 2), 103))), 0,
DATEPART(WEEK, DATEADD(DAY, -82, CONVERT(DATETIME, RIGHT(MLBREF, 2) + '/' + LEFT(RIGHT(MLBREF, 4), 2) + '/' + '20'+LEFT (MLBREF, 2), 103))))
ELSE CONCAT(DATEPART(YEAR, DATEADD(DAY, -82, CONVERT(DATETIME, RIGHT(MLBREF, 2) + '/' + LEFT(RIGHT(MLBREF, 4), 2) + '/' + '20'+LEFT (MLBREF, 2), 103))),
DATEPART(WEEK, DATEADD(DAY, -82, CONVERT(DATETIME, RIGHT(MLBREF, 2) + '/' + LEFT(RIGHT(MLBREF, 4), 2) + '/' + '20'+LEFT (MLBREF, 2), 103))))
END
END
AND (MLSTQT-MLALQT)<>0
GROUP BY DOTWHL, DORELD, MLBREF, DODLDT, DOITNO, DOPPQT, MLSTQT, MMITDS, MLALQT, MBPLCD, MMSTCN, MMCFI2, MMCFI3, DOPSTS, Sales.Sold_Qty, Sales.Forecasted_Qty, MBBUYE, MMRESP, Other_Assortments.item
ORDER BY DOTWHL, DORELD
