Solved

# oracle sqlplus date format

Posted on 2013-07-01

I need to format the poc_date field to be** 5/1/13 **instead of **20130501**

select poc_date, sum(sec_05) sec_05 , sum(sec_1) sec_1, sum(sec_105) sec_105 ,sum(sec_2) sec_2, sum(sec_205) sec_205,

sum(sec_3) sec_3,sum(sec_305) sec_305, sum(sec_4) sec_4,sum(sec_405)sec_405,sum(sec_5)sec_5,sum(sec_6)sec_6,

sum(sec_6_plus) sec_6_plus, sum(total_plm_count) total_plms, sum(total_resp_time_sec) total_resp_sec

from

(

SELECT trunc (poc_start_timestamp)poc_date,

case when RESP_MSEC/plm_cnt/1000 <= 0.5 then plm_cnt else 0 end sec_05,

case when RESP_MSEC/plm_cnt/1000 > 0.5 and RESP_MSEC/plm_cnt/1000 <= 1 then plm_cnt else 0 end sec_1,

case when RESP_MSEC/plm_cnt/1000 > 1 and RESP_MSEC/plm_cnt/1000 <= 1.5 then plm_cnt else 0 end sec_105,

case when RESP_MSEC/plm_cnt/1000 > 1.5 and RESP_MSEC/plm_cnt/1000 <= 2 then plm_cnt else 0 end sec_2,

case when RESP_MSEC/plm_cnt/1000 > 2 and RESP_MSEC/plm_cnt/1000 <= 2.5 then plm_cnt else 0 end sec_205,

case when RESP_MSEC/plm_cnt/1000 > 2.5 and RESP_MSEC/plm_cnt/1000 <= 3 then plm_cnt else 0 end sec_3,

case when RESP_MSEC/plm_cnt/1000 > 3 and RESP_MSEC/plm_cnt/1000 <= 3.5 then plm_cnt else 0 end sec_305,

case when RESP_MSEC/plm_cnt/1000 > 3.5 and RESP_MSEC/plm_cnt/1000 <= 4 then plm_cnt else 0 end sec_4,

case when RESP_MSEC/plm_cnt/1000 > 4 and RESP_MSEC/plm_cnt/1000 <= 4.5 then plm_cnt else 0 end sec_405,

case when RESP_MSEC/plm_cnt/1000 > 4.5 and RESP_MSEC/plm_cnt/1000 <= 5 then plm_cnt else 0 end sec_5,

case when RESP_MSEC/plm_cnt/1000 > 5 and RESP_MSEC/plm_cnt/1000 <= 6 then plm_cnt else 0 end sec_6,

case when RESP_MSEC/plm_cnt/1000 > 6 then plm_cnt else 0 end sec_6_plus,

plm_cnt total_plm_count, resp_msec/plm_cnt/1000 total_resp_time_sec

FROM poc_STATISTIC

where poc_start_timestamp >= to_date('1-May-2013','dd-MON-YYYY')

and poc_start_timestamp < to_date('1-Jun-2013','dd-MON-YYYY')

)

group by poc_date

order by 1

**20130501** 6040 2496 1987 1221 612 290 141 73 55 40 28 51 13034 11767.3513

20130502 5858 2536 2158 1325 550 287 129 62 57 35 24 50 13071 11946.7767