We help IT Professionals succeed at work.
Get Started

Conversion failed when converting date and/or time from character string.

1,148 Views
Last Modified: 2012-08-13
Hi everybody,

Hope this isn't a duplicate question but I have looked throught the answers and couldn't find anything to help me. I am doing an insert from OPENQUERY Oracle source, but it failes due to 'Conversion failed when converting date and/or time from character string.' yet it doesnt tell me the offending field which has me stumped.

I tried reducing the insert to just the date values and oddly it worked, so now I am even more confused than before. Can anyone shed any light on why SQL Server is doing this?




INSERT INTO sales (barcode, purchase_order, order_no, line_no, line_code, line_description, item_no, tag_no, quantity, line_value, total_value, customer_code, project_name, works_month, 
month_year_contractual, order_date, contractual_date, delivery_date, created_by, premium_order, component, outstanding_body, outstanding_diffuser, outstanding_disc, hold, packed_date, misc_ref_1, misc_ref_2, insert_date, updated_date)
(
	SELECT w.*
	FROM OPENQUERY(Welcom,'
	SELECT 
	''5'' || numeric_order_no || line_no as "body_barcode",
	purchase_order as "purchase_order",
	order_no as "order_no",
	line_no as "line_no",
	line_code as "line_code",
	line_description as "line_description",
	item_no as "item_no",
	tag_no as "tag_no",
	quantity as "quantity",
	line_value as "line_value",
	total_value as "total_value",
	customer_code as "customer_code",
	project_name as "project_name",
	works_month as "works_month",
	month_year_contractual as "month_year_contractual",
	order_date as "order_date",
	contractual_date as "contractual_date",
	delivery_date as "delivery_date",
	created_by as "created_by",
	premium_order as "premium_order",
	''Body'' as "component",
	outstanding_body as "outstanding_body",
	outstanding_diffuser as "outstanding_diffuser",
	outstanding_disc as "outstanding_disc",
	hold as "hold",
	packed as "packed",
	misc_ref_1 as "misc_ref_1",
	misc_ref_2 as "misc_ref_2",
	syncronized as "inserted_date",
	syncronized as "updated_date"	
	FROM 
	(
	  SELECT
	  TRIM(UPPER(O.CUSTOMR_RF)) as purchase_order,
	  TRIM(UPPER(L.ORDER_NUM)) as order_no,
	  TRIM(UPPER(L.ORDER_LINE)) as line_no,
	  TRIM(UPPER(L.PROD_CD)) as line_code,
	  TRIM(UPPER(S.PROD_DESC)) as line_description,
	  TRIM(UPPER(L.CUST_ITEM_NO)) as item_no,
	  TRIM(UPPER(L.TAG_NO)) as tag_no, 
	  TRIM(UPPER(L.QTY_ORD)) as quantity,
	  TRIM(UPPER(L.C_UNIT_PRC)) as line_value,
	  TRIM(UPPER(L.H_TOT_VAL)) as total_value,
	  TRIM(UPPER(O.CUSTOMR_ID)) as customer_code,
	  TRIM(UPPER(O.PROJECT_NAME)) as project_name,
	  TRIM(UPPER(''holder'')) as works_month,
	  TRIM(UPPER(''1'')) as month_year_contractual,
	  TRIM(UPPER(TO_CHAR(O.ORDER_DTE,''DD/MM/YYYY''))) as order_date,
	  TRIM(UPPER(TO_CHAR(L.PROM_DTE,''DD/MM/YYYY''))) as contractual_date,
	  TRIM(UPPER(TO_CHAR(L.DLV_DTE,''DD/MM/YYYY''))) as delivery_date,
	  TRIM(UPPER(O.CREATED_BY)) AS created_by,
	  TRIM(UPPER(''HOLDER'')) AS premium_order, 
	  TRIM(UPPER(''HOLDER'')) AS component, 
	  TRIM(UPPER(''MATERIAL HOLDER'')) AS outstanding_body,
	  TRIM(UPPER(''MATERIAL HOLDER'')) AS outstanding_diffuser,
	  TRIM(UPPER(''MATERIAL HOLDER'')) AS outstanding_disc,
	  '''' as hold,
	  TRIM(UPPER(''PACKED'')) AS packed,
	  TRIM(UPPER(L.MISC_REF1)) AS misc_ref_1,
	  TRIM(UPPER(L.MISC_REF2)) AS misc_ref_2,
	  (select to_char(sysdate, ''DD-MM-YYYY HH24:MI:SS'') from dual) as syncronized,
	  REPLACE(TRANSLATE(UPPER(L.ORDER_NUM),''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ,./;''''[]\`~!@#$%^\*()__+{}-|?><'',''0123456789''), '' '', '''') AS numeric_order_no
	  FROM OPORDLN L
	  LEFT JOIN OPORDHD O ON TRIM(UPPER(L.ORDER_NUM)) = TRIM(UPPER(O.ORDER_NUM))
	  LEFT JOIN STPRDDSC S ON TRIM(UPPER(S.PROD_CODE)) = TRIM(UPPER(L.PROD_CD))
	  WHERE TRIM(PROD_CD) LIKE ''NZ'' || ''%''  OR TRIM(PROD_CD) LIKE ''NB'' || ''%''
	  AND UPPER(TRIM(L.ORDER_NUM)) LIKE ''AX'' || ''%''
	)
	WHERE length(numeric_order_no)=5
	order by order_no ASC
	') w
	LEFT JOIN sales s on w.order_no = s.order_no 
	WHERE w.order_no +'-'+ w.line_no NOT IN (SELECT order_no+'-'+line_no FROM sales)
	AND w.body_barcode NOT IN (select s.barcode from sales)

Open in new window

Comment
Watch Question
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 2 Answers and 13 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE