Leogal
asked on
SQL Implicit conversion from data type to datetime to numeric is not allowed
When I attempt the script I wrote below i receive this error:
Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
Any thoughts on how to resolve the error would be most appreciated.
Here is the script:
Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
Any thoughts on how to resolve the error would be most appreciated.
Here is the script:
-- DATABASE
USE DmStagingVD
insert into [dbo].[DMFPURCHLINEENTITY]
SELECT TOP 150
ISNULL([PELINE],' ') as [Line Number]
,ISNULL([PENO],' ') as [Purchase Order]
,convert(datetime,[PECSDT]) as [Delivery Date]
,ISNULL([POCATG], ' ')as [External]
,ISNULL([PEPN],' ') as [Item Number]
,ISNULL([POCOM1],' ') AS NAME
,ISNULL([POCOST],0) as [Unit Price]
,ISNULL([POQTY],0) as [Quantity]
,ISNULL([POVEND],99999999) as [Vendor Account]
,1 as [RecId]
,' ' as [ACCOUNTINGDISTRIBUTIONTEMPLATE]
,' ' as [ActivityNumber]
,' ' as [ASSETBOOKID]
,' ' as [ASSETGROUP]
,' ' as [ASSETID]
,' ' as [BARCODE]
,' ' as [BARCODETYPE]
,' ' as [BLOCKED]
,' ' as [CASETAGGING]
,' ' as [CFOPTABLE_BR_CFOPID]
,' ' as [COMPLETE]
,' ' as [CONFIGID]
,dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01) as [CONFIRMEDDLV]
,0.00 as [CONFIRMEDTAXAMOUNT]
,' ' as [CONFIRMEDTAXWRITECODE]
,' ' as [COUNTYORIGDEST]
,' ' as [COVREF]
,' ' as [CREATEFIXEDASSET]
,' ' as [CURRENCYCODE]
,' ' as [CUSTOMERREF]
,' ' as [CUSTPURCHASEORDERFORMNUM]
,' ' as [DEFAULTDIMENSION]
,' ' as [DEFINITIONGROUP]
,' ' as [DELIVERYADDRESS]
,' ' as [DELIVERYCITY]
,' ' as [DELIVERYCOUNTRYREGIONID]
,' ' as [DELIVERYCOUNTY]
,' ' as [DELIVERYLOCATIONROLE]
,' ' as [DELIVERYNAME]
,' ' as [DELIVERYSTATE]
,' ' as [DELIVERTYSTREET]
,' ' as [DELIVERYTYPE]
,' ' as [DELIVERYZIPCODE]
,dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01)as [DEPRECIATIONSTARTDATE]
,0.00 as [DISCAMOUNT]
,0.00 as [DISCPERCENT]
,' ' as [EDITABLEINWORKFLOW]
,' ' as [EXECUTIONID]
,' ' as [GSTHSTTAXTYPE_CA]
,' ' as [INTERCOMPANYINVENTTRANSID]
,' ' as [INTERCOMPANYORIGIN]
,dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01) as [INTRASTATFULFILLMENTDATE_HU]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,0.00 as [INVENTINVOICENOW]
,' ' as [INVENTLOCATIONID]
,0.00 as [INVENTRECEIVEDNOW]
,' ' as [INVENTREFID]
,' ' as [INVENTREFTRANSID]
,' ' as [INVENTSERIALID]
,' ' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,' ' as [ISDELETED]
,' ' as [ISFINALIZED]
,' ' as [ISINVOICEMATCHED]
,' ' as [ISMODIFIED]
,' ' as [ISPWP]
,' ' as [ISSELECTED]
,' ' as [ITEMBOMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,' ' as [ITEMTAGGING]
,' ' as [LEDGERDIMENSION]
,0.00 as [LINEAMOUNT]
,' ' as [LINEDELIVERYTYPE]
,0.00 as [LINEDISC]
,' ' as [LINEHEADER]
,0.00 as [LINEPERCENT]
,0.00 as [MARKUPTRANS_CALCULATEDAMOUNT]
,' ' as [MARKUPTRANS_CURRENCYCODE]
,' ' as [MARKUPTRANS_DOCUMENTSTATUS]
,' ' as [MARKUPTRANS_INTERCOMPANYCOMPANYID]
,' ' as [MARKUPTRANS_INTERCOMPANYINVOICEID]
,' ' as [MARKUPTRANS_INTERCOMPANYMARKUPUSEVALUE]
,0.00 as [MARKUPTRANS_INTERCOMPANYMARKUPVALUE]
,' ' as [MARKUPTRANS_ISDELETED]
,' ' as [MARKUPTRANS_ISMODIFIED]
,' ' as [MARKUPTRANS_KEEP]
,0 as [MARKUPTRANS_LINENUM]
,' ' as [MARKUPTRANS_MARKUPCATEGORY]
,' ' as [MARKUPTRANS_MARKUPCODE]
,' ' as [MARKUPTRANS_MODULECATEGORY]
,' ' as [MARKUPTRANS_MODULETYPE]
,' ' as [MARKUPTRANS_ORIGTABLEID]
,0.00 as [MARKUPTRANS_POSTED]
,0.00 as [MARKUPTRANS_TAXAMOUNT]
,' ' as [MARKUPTRANS_TAXAUTOGENERATED]
,' ' as [MARKUPTRANS_TAXGROUP]
,' ' as [MARKUPTRANS_TAXITEMGROUP]
,' ' as [MARKUPTRANS_TAXWRITECODE]
,dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01)as [MARKUPTRANS_TRANSDATE]
,' ' as [MARKUPTRANS_TRANSTABLEID]
,' ' as [MARKUPTRANS_TXT]
,0.00 as [MARKUPTRANS_VALUE]
,' ' as [MARKUPTRANS_VOUCHER]
,' ' as [MATCHINGPOLICY]
,0.00 as [MULTILNDISC]
,0.00 as [MULTILNPERCENT]
,' ' as [OPERATIONTYPE_MX]
,0.00 as [OVERDELIVERYPCT]
,' ' as [PALLETTAGGING]
,' ' as [PDSCALCULATIONID]
,0.00 as [PDSCWINVENTRECEIVEDNOW]
,0 as [PDSCWQTY]
,0.00 as [PDSCWREMAININVENTFINANCIAL]
,0 as [PDSCWREMAININVENTPHYSICAL]
,' ' as [PORT]
,0.00 as [PRICEUNIT]
,' ' as [PROJCATEGORYID]
,' ' as [PROJID]
,' ' as [PROJLINEPROPERTYID]
,' ' as [PROJSALESCURRENCYID]
,0.00 as [PROJSALESPRICE]
,' ' as [PROJSALESUNITID]
,' ' as [PROJTAXGROUPID]
,' ' as [PROJTAXITEMGROUPID]
,' ' as [PPROJTRANSID]
,' ' as [PSARETAINSCHEDULEID]
,0.00 as [PSATOTALRETAINAMOUNT]
,' ' as [PURCHASETYPE]
,0.00 as [PURCHMARKUP]
,0 as [PURCHRECEIVEDNOW]
,' ' as [PURCHREQID]
,' ' as [PURCHSTATUS]
,' ' as [PURCHUNIT]
,0 as [RBOPACKAGELINENUM]
,0 as [REMAINDER]
,' ' as [REQATTENTION]
,' ' as [REQPLANIDSCHED]
,' ' as [REQPOID]
,0 as [RETAILLINENUMEX1]
,' ' as [RETAILPACKAGEID]
,0.00 as [RETAILTEMPVALUEEX2]
,' ' as [RETURNACTIONID]
,' ' as [RETURNDISPOSITIONCODEID]
,' ' as [RETURNSTATUS]
,' ' as [ROWID]
,' ' as [SCRAP]
,' ' as [SEARCHKEY]
,' ' as [SERVICEADDRESS]
, dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01) as [SERVICEDATE]
, dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01)as [SHIPPINGDATECONFIRMED]
, dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01) as [SHIPPINGDATEREQUESTED]
,' ' as [SKIPUPDATE]
,0.00 as [STATISTICVALUE_LT]
,' ' as [STATPROCID]
,' ' as [STATTRIANGULARDEAL]
,' ' as [STOCKEDPRODUCT]
,' ' as [SYSTEMENTRYSOURCE]
,0.00 as [TAX1099AMOUNT]
,' ' as [TAX1099BOX]
,' ' as [TAX1099STATE]
,' ' as [TAXGROUP]
,0.00 as [TAX1099STATEAMOUNT]
,' ' as [TAX1099TYPE]
,' ' as [TAXAUTOGENERATED]
,' ' as [TAXITEMGROUP]
, ' ' as [TAXSERVICECODE_BR]
,0.00 as [TAXWITHHOLDBASECUR_TH]
,0.00 as [TAXWITHHOLDGROUP_TH]
,0.00 as [TAXWITHHOLDITEMGROUP_TH]
,' ' as [TRANSACTIONCODE]
,' ' as [TRANSFERSTATUS]
,' ' as [TRANSPORT]
,0.00 as [UNDERDELIVERYPCT]
,' ' as [VARIANTID]
,' ' as [VENDGROUP]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WORKFLOWSTATE]
,' ' as [RECVERSION]
,0 as [PARTITION]
FROM OPENQUERY
(KBM400MFG,
' SELECT
b.PELINE
,b.PENO
,b.PECSDT
,c.POCATG
,b.PEPN
,c.POCO
,d.POCOM1
,c.POCOST
,c.POQTY
,c.POVEND
,b.peco
FROM [KBM400MFG].[FLPODET] B
LEFT OUTER JOIN [KBM400MFG].[FLPOSUM] c ON B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO]
LEFT OUTER JOIN [KBM400MFG].[FLSTCMST] D ON B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO]
where B.[PECO] in(01,03,05,07)
'
) AS A
Tough to debug this without knowing anything about the underlying tables....
However,
1) I don't know what you're trying to accomplish with the statements like this:
dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01) as [CONFIRMEDDLV]
Can you explain, because date add is expecting a date for the 3rd parameter.
2) This:
http://blog.sqlauthority.com/2013/04/09/sql-server-fix-error-217-implicit-conversion-from-data-type-datetime-to-int-is-not-allowed-use-the-convert-function-to-run-this-query/
isn't a direct answer to your question, but it might be a good primer to read anyway.
However,
1) I don't know what you're trying to accomplish with the statements like this:
dateadd(year,0,1901) + dateadd(month,0,01) + dateadd(day,0,01) as [CONFIRMEDDLV]
Can you explain, because date add is expecting a date for the 3rd parameter.
2) This:
http://blog.sqlauthority.com/2013/04/09/sql-server-fix-error-217-implicit-conversion-from-data-type-datetime-to-int-is-not-allowed-use-the-convert-function-to-run-this-query/
isn't a direct answer to your question, but it might be a good primer to read anyway.
Maybe this one too?
0 as [PURCHRECEIVEDNOW]
0 as [PURCHRECEIVEDNOW]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@portletPaul I will modify my code to use the convert function. Thank you for insight.
,0.00 as [INVENTINVOICENOW]
,0.00 as [INVENTRECEIVEDNOW]