Oracle Analytics and AI

You can define the column as String and then just take the 'YYYY-MM-DD' part with the help of Substring function and then again cast it to Date.

CAST(SUBSTRING(CAST("- General Information"."Invoice Entered Date" AS char) FROM 1 FOR 10) AS DATE)

It should only bring the Date part after download into the excel.

Hope this help.

Cheers,

All comments

Nathan CCC

Hi, You can convert it to a string so it is not a datetime?

Are you using 'Data Format' tab to define the column as 'Date'? If yes, instead define the column as Date using Cast in the 'Column Formula'. It should not get the time part after download.

CAST(Invoice Creation Date as DATE)

Hope this help.

Cheers,

Hi @Bhaskar Konar @Nathan CCC, Thank you for your prompt responses.

I have tried to define the 'Invoice Creation Date' column as a date using CAST; however, it is unfortunately not fetching the desired output. Therefore, I modified the column properties to retrieve only the date without the time component, and I am now able to achieve that.

However, I am now stuck with the Excel export, where it still displays the time component in the filter options. I have been trying to resolve this issue but have been unsuccessful. Please help me with this."Additionally, in Excel, when we click on the filter option for that specific column, it shows a tree-level filter that starts with the year, then the month, and finally the date; however, it also includes time for filtering. The client does not want to see or filter by time.

Best Regards,

Vamsi S

Dimple-Nagesh-Support-Oracle

You can define the column as String and then just take the 'YYYY-MM-DD' part with the help of Substring function and then again cast it to Date.

CAST(SUBSTRING(CAST("- General Information"."Invoice Entered Date" AS char) FROM 1 FOR 10) AS DATE)

It should only bring the Date part after download into the excel.

Hope this help.

Cheers,

Hassan El Bouihi-Oracle

Hi Vinoth,

I used the Cast as Date and the export shows the date without the time as shown below:

Thank you

Hassan

Hey @Vinoth Mounagurusamy,

Did you try this, edit the data set in BIP datamodel to locate the DATE column and edit column properties to select display DATE Only and then test the flow

Thanks,
Dimple N

Hi @Bhaskar Konar, @Hassan El Bouihi-Oracle,

Thank you for your prompt response.

I tried the suggested option, and it successfully fetches only the date without the time after exporting it to the Excel using 'Formatted' option. However, the date is incorrect in all the cases. For instance, when an invoice is created on November 21st at 12:17 AM, it incorrectly shows the previous day as November 20th.

Thanks & Regards,

Vamsi S

Hi @Dimple-Nagesh-Support-Oracle,

Thank you for your prompt response.

We are currently working with the OTBI report. I have noted the information regarding the BI Publisher report and will surely refer to it if anything comes up related to BIP reports.

Thanks & Regards,
Vamsi S.

https://community.oracle.com/products/oracleanalytics/discussion/comment/61461#Comment_61461

Hello @Bhaskar Konar,

I happened to tweak the code a bit, and it worked as I desired. Thank you for your support on this matter. A special thanks from my senior as well. PFA code snippet below.

CAST(SUBSTRING(CAST(TIMESTAMPADD(SQL_TSI_HOUR,8, "- General Information"."Invoice Entered Date") ASchar)FROM 1 FOR 10) AS DATE)

Best Regards,

Vamsi S