I want to convert the date format this 2024-03-07T00:00:00.000+00:00 into 7-Mar-2024 as when I use trunc() function even then it returns the same and I dont want to convert it into to_char, how is it possible?
TO_DATE( SUBSTR( your_column , 1, 10 ), 'YYYY-MM-DD')
Are you asking about a Publisher feature or is yours a pure SQL question?
A column of type date, doesn't have a format. The visible format of the date is always a formatting "like a string". There is a default date format mask that is used by the tool you use to view the data.
HI, I am Asking about pure SQL when ever I tried to convert it the date into specific format it returns complete value with time which I dont want
Ok, in the future for SQL questions you should consider posting them in https://forums.oracle.com/ords/apexds/domain/dev-community/category/sql_and_pl_sql (it's a separate platform but still a fully Oracle products/services focused forum).
What data type is your source? "date"? The data type is able to have the time component, that's why by default the display mask for a date field generally contains it.
You said at the beginning you don't want to convert it to a piece of text using TO_CHAR(), despite that function being the one allowing to return a date into the required format.
It sounds like there are missing elements here that aren't known...
Where are you trying to get the date to display the way you want? Does it really matter if the date is rendered as a text if that allows you to render it in the required format?
You said you used TRUNC(), and it does work: it does remove the value of the time component setting it to 00:00:00, but your date is technically still having a time component, it just doesn't have the original hours, minutes and seconds but 00:00:00 instead.
That's why I said that all you seem to be after is to display it differently, because you can't really change the date datatype to not have a time component...
HI @Ayub
BIP default format - canonical format so need to convert refer below for examples -
Can you refer below documentation -
Regards,
Arjun
Hi, We are scheduling a report to be sent out every week. We were wondering if it was possible to keep the latest date that the report ran as a parameter somewhere so that we could use that to only fetch the values in the report which have a latest update time of after the last run of the report? This way we'd only be…
Hi everyone, I'm using Oracle BI Publisher version 12c and recently I notice a strange error: when I schedule some reports that use excel templates (.xls), their output can not be opened without the error message "The file format and extension of <report_name> don't match. The file could be corrupted or unsafe. Unless you…
Hi everyone, We configured a SOAP connection in Oracle Integration Cloud (OIC) using OAuth authentication. However, when we run our integrations, the SOAP invocation fails intermittently. Some integration iterations fail. Most iterations succeed. Sometimes, all iterations complete successfully without any issues. When we…
OTM/GTM Product is currently using OAC for reporting. We wanted to know what is the limitation for oracle BI Publisher rtf template size. We already have verified the documentation : https://docs.oracle.com/en/cloud/paas/analytics-cloud/acpmr/top-faqs-design-publisher-reports.html But wanted to know if this generic figures…
Hi, We are trying to retrieve the complete history Bip report of assignment updates where the same assignment record has been modified multiple times with an unchanged Effective Start Date. We are currently using PER_ALL_ASSIGNMENTS_M and PER_ALL_ASSIGNMENTS_M_; however, the data returned does not consistently reflect all…
