Date format conversion when loading the data from SAP HANA

[Par123]

Hi All,

I am loading a date field BUDAT from sap hana in qlikview.

Its actual format is YYYYMMDD. 

How can I change its date format to YYYY-MM-DD?

I am taking this field in a select statement like

table_name:

SQL Select BUDAT from table;

if I am doing like this

table_name:

SQL Select to_char(BUDAT, 'YYYY-MM-DD') as BUDAT from table;

then qlikview is taking it as text field.

Kindly suggest something as I need to make changes in this select query.

Thanks

[Taoufiq_Zarra]

Hi,

if the returned date is in the form of YYYYMMDD, I suggest you use the following line in LOAD Script:

Date(Date#('BUDAT','YYYYMMDD'),'YYYY-MM-DD') as New_Date

for example for the date 20191209, I get

Cheers,

[Par123]

Hi Taoufiq,

Thanks for the reply. 

I have also implemented this but got'328 invalid name of function or procedure: DATE' error.

I want to use this in a sql statement, the solution you suggested working fine in 'load' statement.

something like:

table_name:

SQL select Date(Date#('BUDAT','YYYYMMDD'),'YYYY-MM-DD') as New_Date from table;

but its throwing error at date function. Is there any other way to do this?

Thanks

[Taoufiq_Zarra]

why not use it in Load instead of SQL ?

[Par123]

I am having a load statement but some dates from other sql queries are coming in correct format, so if I use the Date(Date#(BUDAT,'YYYYMMDD'),'YYYY-MM-DD') as New_Date in that loas statement then the date formats coming as correct will throw error.

[Taoufiq_Zarra · Accepted solution]

Ah, ok

And if you use in load:

If(SubStringCount(BUDAT, '-') >= 1,BUDAT, Date(Date#(BUDAT,'YYYYMMDD'),'YYYY-MM-DD')) as New_Dat

Cheers,

[Par123]

Thanks a lot Taoufiq.

I try that in the load statement and let you know.

[Par123]

Hi Taoufiq,

Your approach is working fine.

Thanks a lot for help.

[Taoufiq_Zarra]

Awesome 🙂