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

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)

Select all Open in new window