BIP: DateTime Format Is Not Proper In BIP Report (Doc ID 2882962.1)

Last updated on MARCH 03, 2024

Applies to:

Goal

How to configure a BIP report,  need DateTime format in IST timezone for all datetime fields. Currently it is showing IST+5.30 hrs.
Even though BI publisher preferences timezone is IST, it is not reflecting in the reports.

We checked for formatting the datetime at report level, but there is no option of DD/MM/YYYY HH24:mm:ss format which is our actual requirement.

So we tried using below formats in datamodel SQL, nothing is working:
1. TO_CHAR(OPEN_DATE,'DD-MON-YYYY HH24:MI:SS') as "Date of Ticket Open",
2. TO_CHAR(LAST_UPDATE_DATE,'DD-MON-YYYY HH:mm:ss') "Last Updated Date",
3. SELECT FROM_TZ( CAST( your_date AS TIMESTAMP ), 'UTC' ) AT TIME ZONE '-05:30'
FROM your_table
4. TO_CHAR(FROM_TZ(CAST(DUE_DATE AS TIMESTAMP), 'UTC') at time zone 'IST', 'DD-MM-YYYY HH:MI:SS')
5. SELECT DATEADD(hh, -5.30 ,LAST_UPDATE_DATE) as "Last Updated Date",
6. select "SVC_SERVICE_REQUESTS"."CREATION_DATE" as "CREATION_DATE"
 from "SVC_SERVICE_REQUESTS" "SVC_SERVICE_REQUESTS"
 where "SVC_SERVICE_REQUESTS"."CREATION_DATE" = "SVC_SERVICE_REQUESTS"."CREATION_DATE" DATEADD(hh, -5.30 ,CREATION_DATE)
7. select SR_ID,CONVERT(datetime, SWITCHOFFSET(DUE_DATE, DATEPART(TZOFFSET,
DUE_DATE AT TIME ZONE 'Indian Standard Time'))) From SVC_SR_MILESTONES

Please suggest the proper format for this issue.
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.