Using CPI DS for loading a file to hana db - Problem with date conversion

[SudarshanS]

HI,

I am getting TEXT file where one of the columns is a date field in the format yyyymmdd .

Need to load this data into HANA DB on SCP.

please advise how to convert this to the format yyyy-mm-dd.

I tried the substring function , but am getting an error on using || for concatenat.. the conversion functions are insufficient for this kind of a conversion.

Appreicate your help.

Thanks

Sudarshan

[btiruveedula · Accepted solution]

Hi Sudarshan,

You have not mentioned the target field datatype (date or varchar).

If the target field datatype is 'date', then 'date to date' transformation is straightforward. So, I will take the case that your target field datatype is 'varchar'.

I have tried converting a 'date' datatype (format YYYYMMDD) to 'varchar' datatype (format YYYY-MM-DD) in the following two ways and both worked:

(i) to_char(Source.Field_TypeDate,'YYYY-MM-DD')

(ii) date_part(Source.Field_TypeDate,'YY') || '-' || date_part(Source.Field_TypeDate,'MM') || '-' || date_part(Source.Field_TypeDate,'DD')

Please see if this helps to you and let me know.

Thanks.

Regards

Bhanu

[former_member592043]

Use to_date function

cuserspraveennkdesktopnutrienpirdate.png

[SudarshanS]

Thank you all for the solutions. I have tried the to_char( <STR> , 'YYYY-MM-DD') option and it worked. However was curious to try the to_date option as well, however the target formatting options do not show 'YYYY-MM-DD' in the drop down. Any idea if this is a limitation?

Regards,

Sudarshan

[former_member423067]

Hi Sudarshan,

you can use to_date. in you case, format should be yyyymmdd.

map the value to a date field.

to_date(<input_string>,'<format>')

with regards,

Nazeer