HOW TO CONVERT 'YYYY-MM-DD' FORMAT TO 'DD/MM/YYYY'

[Former Member]

Hi Experts,

I need to create one calculation view and consume in WEBI. The problem is, in HANA date format is YYYY-MM-DD but in WEBI date format is 'DD/MM/YYYY'.

I can write code to change the date format however i need but it is coming in nvarchar data type but i need it in date datatype so that i can do daysbetween() operation on that.

Please help

[Former Member]

I have a input parameter in clac view and it is returning date datatype in YYYY-MM-DD format but when i am consuming the same clac view in WEBI the input parameter returning date datatype in DD/MM/YYYY format. As a result of this my daysbetween() (written in clac clac) function is not working properly in WEBI.

- i want date in date datatype with format DD/MM/YYYY in WEBI

- on report level

- in clac

Please let me know you need some information.

Thanks

[phu_le_eql]

Hi you have probably already found the answer since it's been 9 months but below is our solution.

We connected WebI 4.2 SP5 to HANA 2.0 calculation view directly by using a Relational Connection (created via IDT).

The column is stored as a date data type in HANA. HANA will treat this as yyyy-mm-dd. In WebI, our BO server settings interpret date objects as dd/mm/yyyy and so it correctly displays the date value in WebI as dd/mm/yyyy without the need for conversion.

If we simply used an OLAP connection instead of the Relational connection and used the date as a Query Prompt filter, WebI's date calender picker returns the value as dd/mm/yyyy but it then validates that against HANA's yyyy-mm-dd which of course there is no match so the report does not return any matching data.

Using the Relational connection fixed the issue for us. Hope this helps you.