CODEWITHSUNDEEP
sqlsql-server
Wayne Posner
Wayne Posner

Reputation: 33

SQL Server 2012--Conversion Failed when converting date and/or time from character string

I've been through all the thread here on this topic and tried all the suggestions, but nothing seems to get rid of this error. Can someone please tell me what's wrong with my sql that is causing this error to be generated? The table I'm trying to insert to is all nvarchar except for the date fields and a couple of integer fields which I'm doing casts on to make sure the types are correct.

SQL is as follows.

insert into [dell.des.SkuOrder] (LineitemKey,IdForSaba,StatusMessage,OrderNumber,OrderDateTime,SalesrepName,OrderTotal,OrderedSkuNumber,
QuantityOrdered, SkuType,DetailSequenceNumber, PartNumber,CustomerNumber,PartQuantity,CustomerNameP,Address1P,Address2P,CityP,StateCode,
FirstNameP,LastNameP,Email,PhoneNumber,CustomerNumberB,CustomerNameB,Address1B,CityB,StateCodeB,ContactNameB, 
FirstNameB, LastNameB,EmailB,PhoneNumberb,SabaCurrency,SabaLocale,SabaOrg,StatusDateTime,
SabaRegion, ExpirationDate,TransactionValue,SabaOrderNumber)

SELECT Distinct 
SKU_TYPE as SkuType, 
PART_ID as PartNumber,
(CUSTOM1 + ' ' + CUSTOM2) as ContactNameB, 
headers.EXT_ID1 as OrderNumber,
CUSTOM1 as FirstNameB,
CUSTOM0 as CustomerNameP,
CURRENCY_ID as SabaCurrency,
CUSTOM1 as FirstNameP,
CUSTOM0 as CustomerNameB,
CASE WHEN ISDATE(EXPIRATION_DATE)=1 then CAST( EXPIRATION_DATE as datetime ) else null end as ExpirationDate,
CUSTOM2 as LastNameP,
CASE WHEN ISNUMERIC (AVL_QTY)=1 then CAST (AVL_QTY as int) else null end as QuantityOrdered,
CUSTOM7 as PhoneNumber,
CUSTOM2 as LastNameB,
CUSTOM3 as Address1P,
CONTACT_EMAIL as Email,
ORG as CustomerNumber,
CUSTOM7 as PhoneNumberB,
CUSTOM3 as Address1B,
CASE WHEN ISNUMERIC(PURCH_QTY)=1 then CAST (PURCH_QTY as int) else null end as PartQuantity,
DetailSequenceNuber=1,
CUSTOM4 as Address2P,
SKU as OrderedSkuNumber,
CONTACT_EMAIL as EmailB,
CUSTOM6 as StateCodeB,
CUSTOM4 as Address2B,
CUSTOM5 as CityP,
TransactionValue=0,
ORG as SabaOrg,
CUSTOM9 as SalesrepName,
LineitemKey=(headers.EXT_ID1 + '_'+ SKU + '_1'),

CASE when ISDATE( ORD_CREATION_DATE)=1 then CAST(ORD_CREATION_DATE as datetime) else null END  as OrderDateTime,
CUSTOM6 as StateCode,
ORG as CustomerNumberB,
StatusMessage = 'Imported ST Order - OPEN',
CASE WHEN ISDATE(headers.UPDATED_ON)=1 then CAST(headers.UPDATED_ON as datetime ) else null end as StatusDateTime,
CUSTOM5 as CityB,
CUSTOM8 as OrderTotal,
headers.EXT_ID1 as IdForSaba,
items.SKU_ORDER_NO as SabaOrderNumber,

locale = CASE
    WHEN LOCALE_NAME = 'English' then 'local000000000000001'
    WHEN LOCALE_NAME = 'Deutsh (German)' then 'local000000000000001'
    WHEN LOCALE_NAME = 'English (United Kingdom)' then 'local00000000000008'
    WHEN LOCALE_NAME = 'Nederlands (Dutch)' then 'local000000000000007'
    WHEN LOCALE_NAME = 'Español (Latin America)' then 'local000000000000006'
    WHEN LOCALE_NAME = '繁體中文 (Chinese Traditional)' then  'local000000000000025'
    WHEN LOCALE_NAME ='日本語 (Japanese)' then  'local0000000000000011'
    WHEN LOCALE_NAME ='简体中文 (Chinese Simplified)' then  'local000000000000005'
    WHEN LOCALE_NAME ='한국어 (Korean)' then  'local000000000000024'
    WHEN LOCALE_NAME ='Português (Brasil)' then  'local000000000000014'
    WHEN LOCALE_NAME ='Français canadien (French Canadian)' then  'local000000000000012'
END


  FROM [dbo].[STOrderHeaders] headers 
  inner join [dbo].[STOrderItems] items on headers.EXT_ID1= items.EXT_ID1

I've spent the past two days working on this trying everything. I know the casts work because the select portion of the query works just fine. I'm not sure if it has anything to do with the fact that the data was imported as UTF-8 in to my two staging tables, but the destination table also contains UTF-8 data.

Any help would be greatly appreciated!

Upvotes: 0

Views: 81

Answers (2)

Wayne Posner
Wayne Posner

Reputation: 33

Thank you! I had the columns out of order and sorting that out got rid of the conversion error.

Upvotes: 0

ab0369
ab0369

Reputation: 206

Insert - 5th column is OrderDateTime
Select- 5th column is FirstNameB

Are you trying to insert wrong value into the DateTime Column ?

Upvotes: 2

Related Questions