Converstion error in sap bods?

dataservices job design
1

Hi Experts,

When I try to convert datatype varchar to datetime. I am getting following error.

SQL submitted to ODBC data source resulted in error <[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed
when converting date and/or time from character string.>. The SQL submitted is <INSERT INTO “DBO”.“TARGET_TABLE” ( “PRODUCT_ID”
, “PRODUCT_NAME” , “PRODCT_DATE” ) SELECT “STAGE_TABLE”.“PRODUCT_ID” , “STAGE_TABLE”.“PRODUCT_NAME” ,
substring(CONVERT(char, CONVERT(datetime, “STAGE_TABLE”.“PRODCT_DATE” , 102) , 20) , 1, 24) FROM “DBO”.“STAGE_TABLE”
“STAGE_TABLE” >.

Here my source database is Sql Server and target database is also Sql server

my data was like this

source

prodcut_date varchar(255)
20170319123456

Target

prodcut_date datetime

I tried in different ways like below but still I am getting error

  1. to_char( to_date( STAGE_TABLE.PRODCT_DATE,‘yyyy.mm.dd’),‘yyyy.mm.dd hh24:mi:ss’)

  2. to_date( STAGE_TABLE.PRODCT_DATE,‘yyyy.mm.dd hh24:mi:ss’)

Could you help me how to convert varchar data value into datatime

Thanks,
Venki

ursfriend77 (BOB member since 2011-03-02)

2

Try:


to_date(STAGE_TABLE.PRODCT_DATE,'yyyymmddhh24miss')

Make sure that the data type for the field this is mapped in is Datetime.

You spelled your field names a couple different ways in your post, so please make sure to pull them from the query (or table) source rather than typing them manually, this will reduce typographical errors in your code.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

3

Thanks, it is working fine

Regards,
Venki

ursfriend77 (BOB member since 2011-03-02)