We help IT Professionals succeed at work.

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

wellso
wellso asked
on
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

View Solutions Only

SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
Ok, Replace SELECT w.* with actual column names from the inner select statement.
And you need to handle the datetime columns correctly in the column name where you are going to replace *

        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,

For these columns, place

CONVERT(datetime, order_date, 103) as order_date

like

SELECT w."body_barcode",  "purchase_order", ... other_columns_list...,
CONVERT(datetime, order_date, 103) as order_date,
....

so that they become valid datetime values to get it fixed..
Dale BurrellDirector

Commented:
Thats a tough one, the only way I know how to solve that is to use a query to obtain a sample of the dates you are trying to insert and then inspect them for invalid formats.

If you'e not sure which formats are valid test them in select convert(datetime, MyDateString) to see. As you find invalid formats you need to convert them to something useful or filter them out. With any luck you will only find a couple of invalid formats to handle and then the query will work.

Well, actually that all depends on the datasource - if the original data is very bad (e.g. user entered string) you could have a lot of work ahead of you.

HTH
rupvis

Commented:
Can i know why you have double quotes ''DD/MM/YYYY'' when only single quotes are needed?
cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Sql Server doesn't actually like the dd/mm/yyyy format, anything that looks like xx/yy/zzzz is interpreted by default as mm/dd/yyyy unless you have SET the language option, e.g. SET LANGUAGE British.

Change the three DD/MM/YYYY formats to YYYYMMDD (iso universal)
e.g.
        TRIM(UPPER(TO_CHAR(O.ORDER_DTE,''YYYYMMDD''))) as order_date,

For the last one, use this format

        (select to_char(sysdate, ''YYYYMMDD HH24:MI:SS'') from dual) as syncronized,

Regards
wellso

Author

Commented:
Forgot the mention the double quotes are actually two single ones, used as escape characters with OPENQUERY. Going to go through the solutions now, thanks for the help everybody.
wellso

Author

Commented:
I ahve tried both the CONVERT solution and the YYYYMMDD solution and both still produce the same error. As I mentioned I tried an insert with only the date collumns and it worked, maybe my table definition is off?


	[barcode] [int] NULL,
	[order_no] [nvarchar](250) NULL,
	[line_no] [int] NULL,
	[line_code] [nvarchar](250) NULL,
	[line_description] [nvarchar](250) NULL,
	[item_no] [nvarchar](250) NULL,
	[tag_no] [nvarchar](250) NULL,
	[quantity] [int] NULL,
	[line_value] [nvarchar](250) NULL,
	[total_value] [nvarchar](250) NULL,
	[purchase_order] [nvarchar](250) NULL,
	[customer_code] [nvarchar](250) NULL,
	[project_name] [nvarchar](250) NULL,
	[works_month] [nvarchar](250) NULL,
	[month_year_contractual] [int] NULL,
	[order_date] [date] NULL,
	[contractual_date] [date] NULL,
	[delivery_date] [date] NULL,
	[packed_date] [date] NULL,
	[insert_date] [datetime] NULL,
	[updated_date] [datetime] NULL,
	[created_by] [nvarchar](250) NULL,
	[component] [nvarchar](50) NULL,
	[outstanding_body] [nvarchar](250) NULL,
	[outstanding_diffuser] [nvarchar](250) NULL,
	[outstanding_disc] [nvarchar](250) NULL,
	[premium_order] [nvarchar](250) NULL,
	[hold] [nvarchar](250) NULL,
	[misc_ref_1] [nchar](250) NULL,
	[misc_ref_2] [nchar](250) NULL,
	[type] [nvarchar](50) NULL,
	[size] [nvarchar](50) NULL,
	[rating] [nvarchar](50) NULL,
	[body_seat] [nvarchar](50) NULL,
	[disc_seat] [nvarchar](50) NULL,
	[end_connection] [nvarchar](50) NULL,
	[spring] [nvarchar](50) NULL,
	[spring_torque] [nvarchar](50) NULL,
	[wetted_parts] [nvarchar](50) NULL,
	[flow_diffuser] [nvarchar](50) NULL

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
Have you tried out my very first comment to use CONVERT function..
wellso

Author

Commented:
yes mate, this is my query now:


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.body_barcode, 
	w.purchase_order, 
	w.order_no, 
	w.line_no, 
	w.line_code, 
	w.line_description, 
	w.item_no, 
	w.tag_no, 
	w.quantity, 
	w.line_value, 
	w.total_value, 
	w.customer_code, 
	w.project_name, 
	w.works_month, 
	w.month_year_contractual, 
	CONVERT(datetime, w.order_date, 103) as order_date, 
	CONVERT(datetime, w.contractual_date, 103) as contractual_date,
	CONVERT(datetime, w.delivery_date, 103) as delivery_date,
	w.created_by, 
	w.premium_order, 
	w.component, 
	w.outstanding_body, 
	w.outstanding_diffuser, 
	w.outstanding_disc, 
	w.hold, 
	CONVERT(datetime, w.packed, 103) as packed, 
	w.misc_ref_1, 
	w.misc_ref_2, 
	CONVERT(datetime, w.inserted_date, 100) as inserted,
	CONVERT(datetime, w.updated_date, 100) as updated
	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,
	  null 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 s.order_no +'-'+ s.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

wellso

Author

Commented:
is there any way of increading the depth of error reporting to find the offending collumn?
cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
I would stick with the original query in your question.  Once you have the date strings in YYYYMMDD format, there is no need to perform any conversion.
It's like writing a query in SQL server with   select * from tbl where date='20100611'
You don't have to write it as   select * from tbl where date=convert(datetime,'20100611',<format>)

Back to your question, make the changes in http:#a32968253, and could you also answer these questions?

What are the column data types for:
 packed_date
 updated_date

Is there a reason for the ( open bracket in line 3?  It appears unmatched

Try running the query without the insert into and check that it is OK
wellso

Author

Commented:
Sorry, missed out the closing bracket.

data type for packed date is date,
updated_date is datetime

this is my query now:

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,''YYYYMMDD''))) as order_date,
        TRIM(UPPER(TO_CHAR(L.PROM_DTE,''YYYYMMDD''))) as contractual_date,
        TRIM(UPPER(TO_CHAR(L.DLV_DTE,''YYYYMMDD''))) 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,
        null 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, ''YYYYMMDD 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 s.order_no +'-'+ s.line_no NOT IN (SELECT order_no+'-'+line_no FROM sales)
      AND w.body_barcode NOT IN (select s.barcode from sales)
)
wellso

Author

Commented:
Think it was a formatting error coming from Oracle, thanks for your help anyway guys
cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Don't know why you closed the question with the comment you did, but

data type for packed date is date,
updated_date is datetime

Means that you were trying to populate them with

        TRIM(UPPER(''PACKED'')) AS packed,  <<< into packed_date

REPLACE(TRANSLATE(UPPER(L.ORDER_NUM),''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ,./;''''[]\`~!@#$%^\*()__+{}-|?><'',''0123456789''), '' '', '''') AS numeric_order_no  <<< into updated_date

Which is where I believe the problem is.  Not sure where the Accepted Answer is relevant here doing any CONVERT at the SQL Server end.