Oracle Analytics and AI

PD001

How to add date parameter in MON-YY format in dashboard in oracle fusion? We are taking date from the below SQL query which is having 'mm/dd/yyyy' format

'SELECT
"General Ledger - Period Status Real Time"."Period Statuses"."Start Date"
FROM "General Ledger - Period Status Real Time"'.

Gurujothi D

Hello,

Below is the working example which will return 04/01/2024 as APR-24 and 12th period as MAR-25. Update this as per your configuration.

SELECT concat(case when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=01 then 'APR'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=02 then 'MAY'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=03 then 'JUN'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=04 then 'JUL'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=05 then 'AUG'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=06 then 'SEP'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=07 then 'OCT'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=08 then 'NOV'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=09 then 'DEC'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=10 then 'JAN'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=11 then 'FEB'
when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=12 then 'MAR'

			end,

concat('-',SUBSTRING(CAST(Year("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as CHAR(4)) FROM 3 FOR 2))) FROM "General Ledger - Period Status Real Time"

I hope this is helpful.

Regards,
Guru