convert(datetime,****)运行报错
694次浏览
编辑于2015年12月29日 11:06:09
在ksql中执行下列语句
select inven.fid as invenId,
inven.fbillentryid as billEntryId,
material.fid as materialId,
material.fname_l2 as materialname,
material.fnumber as materialNum,
material.fmodel as materialmodel,
inven.flot as flot,
inven.fstorageorgunitid as storageOrgUnitId,
storage.fname_l2 as storageOrgUnit,
inven.fwarehouseid as warehouseId,
warehouse.fname_l2 as warehouse,
location.fname_l2 as locationName,
inven.fbaseqty as baseQty,
unit.fname_l2 as baseUnit,
inven.fcurStoreAssistQty as assQty,
assunit.fname_l2 as assUnit,
storestate.fname_l2 as stockStatus ,
to_char(manue.FStockReason) as FStockReason,
cus.fname_l2 customername,
manu.fnumber as manuBillNumber,
manue.fchemicelement35 as steelCore,
manu.FAuditTime as storageTime,
manu.fcreatetime as manucreatetime,
to_char(manue.fboxnumber) as boxNumber,
manue.ftheoryqty theoryqty,
to_char(manue.fpackageSize) as packageSize,
manue.fsaleorderentryseq as orderentryseq,
manue.fdeliverytime as deliverytime,
manue.fgrossQty as grossQty,
manue.fassistQty as maassQty,
manue.fsendDate as sendDate,
to_char(uc.fname_l2) as userCategoryName,
manue.fjoint joint,
manue.fremark as remark ,
manue.fmarketingRemark as fmarketingRemark,
sa.fnumber as orderNumber,
sa.fbizdate as orderdate,
sae.forderedqty as orderQty,
sae.fbaseqty as sabaseQty,
sae.fdeliveryDate as deliveryDate,
case when sae.fdeliveryDate is null then 0 else
round(datediff(convert(datetime,sae.FDeliveryDate),convert(datetime,now()))/84600,0) end as factLeadTimes,
slit.Cfparentvolumenumber parentVolumeNumber,
alloy.fname_l2 alloyName,
slitEntry.Cfslittingroll slittingRoll,
slitQcEntry.Cfinfactthicknes/1000 infcatThickness,
slitQcEntry.CFINFACTWIDTH as infactWidth,
slitQcEntry.CFPIECEQTY as pieceQty,
slitQcEntry.CFPINHOLE as pinhole ,
slitQcEntry.CFTHICKESS as thickess,
slitQcEntry.CFWIDTH as width
from t_im_inventory inven
left join T_DB_WAREHOUSE warehouse on inven.fwarehouseid = warehouse.fid
left join t_db_location location on inven.flocationid=location.fid
left join t_bd_material material on inven.fmaterialid=material.fid
left join T_ORG_Storage storage on inven.fstorageorgunitid=storage.fid
left join T_IM_STORESTATE storestate on inven.FStoreStatusID = storestate.fid
left join T_BD_MeasureUnit unit on inven.fbaseunitid = unit.fid
left join T_BD_MeasureUnit assunit on inven.fassistunitid = assunit.fid
left join t_im_manufacturerecbillentry manuE on inven.fbillentryid = manuE.Fid
left join t_im_manufacturerecbill manu on manuE.Fparentid = manu.fid
left join T_IV_UseCategory uc on manue.fusecategoryid=uc.fid
left join t_bd_customer cus on manue.fcustomerid=cus.fid
left join t_sd_saleorderentry sae on sae.fparentid = manue.fsalebillnumberid and sae.fseq = manue.fsaleorderentryseq
left join t_sd_saleorder sa on sa.fid = manue.fsalebillnumberid
left join Ct_Pm_Slitexecuteentry slitEntry on slitEntry.Cflot = inven.flot
left join CT_PM_SlitExecute slit on slit.fid = slitEntry.Fparentid
left join ct_bd_alloymanager alloy on alloy.fid = slit.cfparentalloyid
left join ct_pqc_slittingqcbillentry slitQcEntry on slitQcEntry.Cfsourcebillentryid = slitEntry.Fid
left join Ct_Pqc_Slittingqcbill slitQc on slitQcEntry.Fparentid = slitQc.Fid
where inven.fbaseqty <> 0 and storage.fnumber in ('K3011','K3031')
and material.fnumber like '168%' and manuE.Fid is not null
执行报错,报错信息如下
complete! use time: 0 end time: 2015-12-29 10:46:34ORA-01858: a non-numeric character was found where a numeric was expected
报错的sql语句是
round(datediff(convert(datetime,sae.FDeliveryDate),convert(datetime,now()))/84600,0) end as factLeadTimes,
其中sae.FDeliveryDate的字段类型是时间戳timestamp
请问这个要怎么写才不报错
select inven.fid as invenId,
inven.fbillentryid as billEntryId,
material.fid as materialId,
material.fname_l2 as materialname,
material.fnumber as materialNum,
material.fmodel as materialmodel,
inven.flot as flot,
inven.fstorageorgunitid as storageOrgUnitId,
storage.fname_l2 as storageOrgUnit,
inven.fwarehouseid as warehouseId,
warehouse.fname_l2 as warehouse,
location.fname_l2 as locationName,
inven.fbaseqty as baseQty,
unit.fname_l2 as baseUnit,
inven.fcurStoreAssistQty as assQty,
assunit.fname_l2 as assUnit,
storestate.fname_l2 as stockStatus ,
to_char(manue.FStockReason) as FStockReason,
cus.fname_l2 customername,
manu.fnumber as manuBillNumber,
manue.fchemicelement35 as steelCore,
manu.FAuditTime as storageTime,
manu.fcreatetime as manucreatetime,
to_char(manue.fboxnumber) as boxNumber,
manue.ftheoryqty theoryqty,
to_char(manue.fpackageSize) as packageSize,
manue.fsaleorderentryseq as orderentryseq,
manue.fdeliverytime as deliverytime,
manue.fgrossQty as grossQty,
manue.fassistQty as maassQty,
manue.fsendDate as sendDate,
to_char(uc.fname_l2) as userCategoryName,
manue.fjoint joint,
manue.fremark as remark ,
manue.fmarketingRemark as fmarketingRemark,
sa.fnumber as orderNumber,
sa.fbizdate as orderdate,
sae.forderedqty as orderQty,
sae.fbaseqty as sabaseQty,
sae.fdeliveryDate as deliveryDate,
case when sae.fdeliveryDate is null then 0 else
round(datediff(convert(datetime,sae.FDeliveryDate),convert(datetime,now()))/84600,0) end as factLeadTimes,
slit.Cfparentvolumenumber parentVolumeNumber,
alloy.fname_l2 alloyName,
slitEntry.Cfslittingroll slittingRoll,
slitQcEntry.Cfinfactthicknes/1000 infcatThickness,
slitQcEntry.CFINFACTWIDTH as infactWidth,
slitQcEntry.CFPIECEQTY as pieceQty,
slitQcEntry.CFPINHOLE as pinhole ,
slitQcEntry.CFTHICKESS as thickess,
slitQcEntry.CFWIDTH as width
from t_im_inventory inven
left join T_DB_WAREHOUSE warehouse on inven.fwarehouseid = warehouse.fid
left join t_db_location location on inven.flocationid=location.fid
left join t_bd_material material on inven.fmaterialid=material.fid
left join T_ORG_Storage storage on inven.fstorageorgunitid=storage.fid
left join T_IM_STORESTATE storestate on inven.FStoreStatusID = storestate.fid
left join T_BD_MeasureUnit unit on inven.fbaseunitid = unit.fid
left join T_BD_MeasureUnit assunit on inven.fassistunitid = assunit.fid
left join t_im_manufacturerecbillentry manuE on inven.fbillentryid = manuE.Fid
left join t_im_manufacturerecbill manu on manuE.Fparentid = manu.fid
left join T_IV_UseCategory uc on manue.fusecategoryid=uc.fid
left join t_bd_customer cus on manue.fcustomerid=cus.fid
left join t_sd_saleorderentry sae on sae.fparentid = manue.fsalebillnumberid and sae.fseq = manue.fsaleorderentryseq
left join t_sd_saleorder sa on sa.fid = manue.fsalebillnumberid
left join Ct_Pm_Slitexecuteentry slitEntry on slitEntry.Cflot = inven.flot
left join CT_PM_SlitExecute slit on slit.fid = slitEntry.Fparentid
left join ct_bd_alloymanager alloy on alloy.fid = slit.cfparentalloyid
left join ct_pqc_slittingqcbillentry slitQcEntry on slitQcEntry.Cfsourcebillentryid = slitEntry.Fid
left join Ct_Pqc_Slittingqcbill slitQc on slitQcEntry.Fparentid = slitQc.Fid
where inven.fbaseqty <> 0 and storage.fnumber in ('K3011','K3031')
and material.fnumber like '168%' and manuE.Fid is not null
执行报错,报错信息如下
complete! use time: 0 end time: 2015-12-29 10:46:34ORA-01858: a non-numeric character was found where a numeric was expected
报错的sql语句是
round(datediff(convert(datetime,sae.FDeliveryDate),convert(datetime,now()))/84600,0) end as factLeadTimes,
其中sae.FDeliveryDate的字段类型是时间戳timestamp
请问这个要怎么写才不报错
推荐阅读