I am trying to output the date (day/mon/yr) without the (hr:min:sec) in the string in oracle BI Publisher. I have tried several different methods of formatting, but nothing seems to work. Here is my code:
select "DATA_POINT_DAILY_AVG"."DATE_OF_AVG" as "DATE_OF_AVG",
"DATA_POINT_DAILY_AVG"."VALUE" as "DAILY_AVG_VALUE"
from "TEST"."CALCULATED_DATA_POINT" "CALCULATED_DATA_POINT",
"TEST"."DATA_POINT_DAILY_AVG" "DATA_POINT_DAILY_AVG"
where "DATA_POINT_DAILY_AVG"."CALCULATED_DATA_POINT_ID"="CALCULATED_DATA_POINT"."ID"
and "DATA_POINT_DAILY_AVG"."SITE_ID" in ('123abc')
and "DATA_POINT_DAILY_AVG"."DATE_OF_AVG" between ('01-FEB-17') and ('28-FEB-17')
and "CALCULATED_DATA_POINT"."NAME" ='test_uptime'
order by "DATA_POINT_DAILY_AVG"."DATE_OF_AVG" DESC;
Here is my current output:
DATE_OF_AVG DAILY_AVG_VALUE
------------------------------- ---------------
(2017-02-28T00:00:00.000+00:00) 100
(2017-02-27T00:00:00.000+00:00) 99
(2017-02-26T00:00:00.000+00:00) 99
(2017-02-25T00:00:00.000+00:00) 99
(2017-02-22T00:00:00.000+00:00) 99
(2017-02-21T00:00:00.000+00:00) 94
(2017-02-20T00:00:00.000+00:00) 99
(2017-02-19T00:00:00.000+00:00) 99
(2017-02-18T00:00:00.000+00:00) 100
Expected output:
DATE_OF_AVG DAILY_AVG_VALUE
----------- ---------------
28-FEB-17 100
27-FEB-17 99
26-FEB-17 99
25-FEB-17 99
22-FEB-17 99
21-FEB-17 94
20-FEB-17 99
19-FEB-17 99
18-FEB-17 100
Any help would be greatly appreciated.
Thanks.
to_char(date_col, 'DD-MON-YY')