>>Hello all,
>>
>>I have a stored procedure that I run in SQL 6.5 and works very well. Yet I run the same stored procedure in SQL 7.0 and I get datetime conversion errors with the same data. Could it be the data?? Could it be the differences in versions?? I'm open to any suggestions.
>>
>>TIA
>
>You may want to post the code...

Here it is

CREATE PROCEDURE SpecialReportcogs @DateFrom char(10), @DateTo char(10),
@OrderType char(1),
@Code2From char(21), @Code2To char(21) WITH RECOMPILE AS



SET FORCEPLAN ON

/* Select shipments for the date range and order type */

CREATE TABLE #lo1
( warehouse CHAR(3) NOT NULL,
batch CHAR(7) NOT NULL,
salesord CHAR(16) NULL,
deldate CHAR(10) NULL ,
consignee CHAR(30) NULL )

INSERT INTO #lo1
SELECT a.warehouse,
a.batch,
a.salesord,
a.deldate,
a.consignee
FROM loadout a
WHERE deldate != ' / / '
and a.shipstatus = 'Y'

and a.salesord != ' '
and a.wo = ' '
and convert(datetime,a.deldate,103) >= convert(datetime,@DateFrom,103)
and convert(datetime,deldate,103) <= convert(datetime,@DateTo,103)
and ( (UPPER(@OrderType) IN ('S','M','J')

and substring(a.salesord,1,1)=UPPER(@OrderType ) )
or (UPPER(@OrderType ) = 'O' and a.salesord between '0' and '9999999999999999')
or (UPPER(@OrderType) = 'D' and substring(a.salesord,1,1) in ('I','W','A','T','X','V','D'))
or (UPPER(@OrderType) = 'N' and substring(a.salesord,1,1) between 'A' and 'z')
or UPPER(@OrderType) = 'A')


/* Add details to shipments */

CREATE TABLE #shp1
( warehouse CHAR(3) NOT NULL,
batch CHAR(7) NOT NULL,
salesord CHAR(16) NULL,
deldate CHAR(10) NULL ,
consignee CHAR(30) NULL,
qty INT NULL,
key1 CHAR(16) NULL,
code2 CHAR(21) NULL,
descrip CHAR(30) NULL,
zz_cost NUMERIC(8,3) NULL )



INSERT INTO #shp1
SELECT a.warehouse,
a.batch,
a.salesord,
a.deldate,
a.consignee,
sum(b.qty) ,
c.key1,
c.code2,
e.descrip,
c.zz_cost
FROM #lo1 a
JOIN inv_trn b (index = BatchWarehouse) on a.batch = b.batch and a.warehouse = b.warehouse
JOIN inv_mst c (index = BSMWarehouse) on b.bsm = c.bsm and b.warehouse = c.warehouse
JOIN code2 e (index = code2Warehouse) on c.code2 = e.code2 and c.warehouse = e.warehouse
WHERE b.bsm != ' ' and
c.code2 between @Code2From and case when @Code2To = ' ' then 'zzzzzzzzzzzzzzzzzzzzz' else @Code2To end
GROUP BY a.warehouse,a.batch, a.salesord, a.deldate, a.consignee, c.key1, c.code2, e.descrip, c.zz_cost
ORDER BY c.code2



/*Bring the cost of POP components*/

CREATE TABLE #pop1
( Warehouse CHAR(3) NOT NULL,
WO# CHAR(7) NOT NULL,
WOQty INT NULL,
KitLot# CHAR(16) NULL ,
Kit CHAR(21) NULL,
KitDescr CHAR(30) NULL,
KitQty INT NULL,
KitCost NUMERIC(8,3) NULL,
Assembly NUMERIC(8,3) NULL,
Rework NUMERIC(8,3) NULL,
Dupe NUMERIC(8,3) NULL,
Outbound# CHAR(7) NOT NULL,
CompLot# CHAR(16) NULL,
Comp CHAR(21) NULL,
CompDescr CHAR(30) NULL,
CompQty INT NULL,
CompCost NUMERIC(8,3) NULL )



INSERT INTO #pop1
SELECT aa.*,
ba.batch as Outbound#,
da.key1 as CompLot#,
ca.code2 as Comp,
ea.descrip as CompDescr,
sum(ca.qty) as CompQty,
da.zz_cost as CompCost
FROM (select a.warehouse as Warehouse,
a.batch as WO#,
a.qty as WOQty,
d.key1 as KitLot#,
c.code2 as Kit,
e.descrip as KitDescr,
sum(c.qty) as KitQty,
isnull(d.zz_cost,0) as KitCost,
isnull(d.zz_ass,0) as Assembly,
isnull(d.zz_rework,0) as Rework,
isnull(e.zz_dupe,0) as Dupe
from workord a (index = EntryDate)
join loadin b (index = WOWarehouse) on a.batch = b.wo and a.warehouse = b.warehouse
join inv_trn c (index = BatchWarehouse) on b.batch = c.batch and b.warehouse = c.warehouse

join inv_mst d (index = BSMWarehouse) on c.bsm = d.bsm and c.warehouse = d.warehouse
join code2 e (index = code2Warehouse) on c.code2 = e.code2 and c.warehouse = e.warehouse
where b.shipstatus = 'Y' and c.bsm != ' ' and c.code2 LIKE '%POP%'
group by a.custcode, a.warehouse, a.batch, a.qty, c.code2,
d.key1, d.zz_cost, d.zz_ass, d.zz_rework, e.descrip, e.zz_dupe) aa

LEFT JOIN loadout ba (index = WOWarehouse) on aa.WO# = ba.wo and aa.Warehouse = ba.Warehouse
JOIN inv_trn ca (index = BatchWarehouse) on ba.batch = ca.batch and ba.Warehouse = ca.Warehouse
JOIN inv_mst da (index = BSMWarehouse) on ca.bsm = da.bsm and ca.warehouse = da.warehouse
JOIN code2 ea (index = code2Warehouse) on ca.code2 = ea.code2 and ca.warehouse = ea.warehouse
WHERE (ba.shipstatus = 'Y') and ca.bsm != ' '
GROUP BY aa.Warehouse, aa.WO#, aa.WOQty, aa.KitLot#, aa.Kit, aa.KitDescr, aa.KitQty, aa.KitCost, aa.Assembly,
aa.Rework, aa.Dupe, ba.batch, da.key1, ca.code2, ea.descrip, da.zz_cost


/*Explode POP's into components*/

CREATE TABLE #pop2
( warehouse CHAR(3) NOT NULL,
batch CHAR(7) NOT NULL,
salesord CHAR(16) NULL,
deldate CHAR(10) NULL ,
consignee CHAR(30) NULL,
qty INT NULL,
key1 CHAR(16) NULL,
code2 CHAR(21) NULL,
descrip CHAR(30) NULL,
zz_cost NUMERIC(8,3) NULL )

INSERT INTO #pop2
SELECT a.warehouse,
a.batch,
a.salesord,
a.deldate,
a.consignee,
a.qty * ISNULL(c.qty,1) as qty,
'POP ' as key1,
ISNULL(b.Comp,a.code2) as code2,
ISNULL(e.descrip,'') as descrip,
AVG(b.compcost) as zz_cost
FROM #shp1 a
LEFT JOIN #pop1 b on a.code2 = b.Kit and a.warehouse = b.warehouse
JOIN kits c on a.code2 = c.code2 and b.Comp = c.c2part and a.warehouse = c.warehouse
JOIN code2 e on b.comp = e.code2 and a.warehouse = e.warehouse
WHERE a.code2 LIKE '%POP%'
GROUP BY a.warehouse, a.batch, a.salesord, a.deldate, a.consignee, a.qty, a.code2, c.qty,
b.comp, e.descrip


/*Show results*/

SELECT a.warehouse,
a.batch,
a.salesord,
a.deldate,
a.consignee,
a.qty,
a.key1,

a.code2,
a.descrip,
a.zz_cost
FROM #shp1 a
WHERE a.code2 NOT LIKE '%POP%'
UNION ALL
SELECT a.warehouse,
a.batch,

a.salesord,
a.deldate,
a.consignee,
a.qty,
a.key1,
a.code2,
a.descrip,
a.zz_cost
FROM #pop2 a

SET FORCEPLAN OFF