Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Implicit conversion from data type to datetime to numeric is not allowed

Posted on 2014-10-01
5
Medium Priority
?
419 Views
Last Modified: 2014-10-07
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:

-- 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 
		                                                          

Open in new window

0
Comment
Question by:Leogal
5 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40355546
Are these supposed to be dates?

,0.00 as [INVENTINVOICENOW]
,0.00 as [INVENTRECEIVEDNOW]
0
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 40355549
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.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40355551
Maybe this one too?

0 as [PURCHRECEIVEDNOW]
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40356496
Implicit conversion from data type datetime to numeric is not allowed.

this error's "direction" says FROM datetime TO numeric

But I cannot see anything "in that direction" in your code, so I'm pretty sure it has to be that the data type of one of your columns does not match the data type of the table.

this
, DATEADD(YEAR, 0, 1901) + DATEADD(MONTH, 0, 01) + DATEADD(DAY, 0, 01)
results in
March, 19 1905

i.e. Overall you are adding (1901+1+1) days to Jan 1, 1900

Instead of using dateadd() here just use convert() with a date literal

e.g. convert(datetime,'19000101',112)

for other date style numbers
0
 

Author Comment

by:Leogal
ID: 40357608
@portletPaul I will modify my code to use the convert function. Thank you for insight.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question