Solved

oracle sqlplus date format

Posted on 2013-07-01
4
600 Views
Last Modified: 2013-07-06
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
0
Comment
Question by:bkreynolds48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 39290664
Have a look at the to_char function.

Examples and formatting etc here: http://www.techonthenet.com/oracle/functions/to_char.php

You can also refer to the Oracle documentation on Date/Time formatting:

http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_commands_1030.htm#BABEFHFG

(That references some OLAP specific stuff but the Date/Time formatting stuff is in there for use with TO_CHAR - I just couldn't put my hands on the more generic one)

EDIT: Found it!
http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements004.htm#i34924
0
 
LVL 1

Assisted Solution

by:bkreynolds48
bkreynolds48 earned 0 total points
ID: 39290731
where would you put the to_char?
0
 
LVL 35

Accepted Solution

by:
YZlat earned 250 total points
ID: 39290893
SELECT to_char (poc_date, "MM/dd/yy") ...


I'd suggest trying something like this:

select to_char(to_date(poc_date,'yyyyMMdd'), 'MM/dd/yy') ...
0
 
LVL 1

Author Closing Comment

by:bkreynolds48
ID: 39303709
thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question