time formatting in oracle- output to excel format

select s.city_name city,s.state_code  st,
       initcap(mc.udf1)  county,
       to_char(drive_date,'MM/DD/RRRR') drive_date,
	   to_char(start_time,'HH12:MI AM') start_date,
	   to_char(end_time,'HH12:MI AM') end_date,
	   site_name as location,
                   s.addr_1 || '       ' ||s.addr_2 address,
                   first_name||' '||last_name||' '||'('||substr(phone_number,1,3)||')'
	   ||substr(phone_number,4,3)||'-'||substr(phone_number,7) contact,
	   case when coach_drive in ('C', 'M') then 'YES'
	        else 'NO' 
	   END mobile_coach,
	   group_name sponsoring_group,
decode(ONLINE_SCHED_DATE,null,null,'https://ht.heartlandbc.org/donor/schedules/drive_schedule/'||bd.drive_id) as weblink
   from blood_drives bd, sites  s , 
        master_codes mc, area_reps ar,groups g
  where drive_date between '01-mar-2015' and '31-mar-2015'
    and bd.site_code = s.site_code
	and ar.area_rep_id = bd.area_rep_no
	and sponsor_group_code = group_code
    AND OPEN_TO_PUBLIC = 'Y'
    AND DRIVE_CANCELLED IS NULL 
	and code_type = 'COUNT'
	and mc.code = s.county_code
	order by 1,4
	

Open in new window



start date    end date

08:30 AM    01:00 PM

I need the time to be formatted to

8:30 AM       1:00 PM
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
Try:
  LTRIM(to_char(start_time,'HH12:MI AM') ,'0') . . .

Open in new window

0
sdstuberCommented:
use the "FM" formatting option around the hours

to_char(your_date_column,'fmHH12fm:MI AM')
0
HuaMin ChenProblem resolverCommented:
Try
...
           replace(to_char(start_time,'HH12:MI AM'),'01:','1:') start_date,
         replace(to_char(end_time,'HH12:MI AM'),'01:','1:') end_date,
...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

anumosesAuthor Commented:
I send the report output as excel spreadsheet. Here the time shows as 3:00:00 PM (example ) when I click on the cell. That is a problem. How can I format that. I tried all different methods which did not work. import
0
anumosesAuthor Commented:
I tried all the above options mentioned by experts. The problem is excel output still puts that :00 after hour and minutes. I am using this report to upload drives on our website via mail merge. There Its a pain to remove :00 for all the drives.
0
johnsoneSenior Oracle DBACommented:
As this seems to be an Excel problem, and not really an Oracle problem, I found this link:

http://www.excelfunctions.net/Excel-Timevalue-Function.html

The function described there is how Excel converts text to a time value.  In all the examples, they show a 24 hour time.  Not a 12 hour time.  I would suggest trying to use a 24 hour time in the file, then after importing,  you can change the display format of the column in Excel to be a 12 hour time.
0
sdstuberCommented:
that's an excel "feature",  if the data "looks like time" then Excel will treat it as time and format the value by its rules

it has nothing to do with Oracle, you'll have to do it in Excel


either change that column type to text, or set the time formatting for that column to not include seconds.
0
johnsoneSenior Oracle DBACommented:
Maybe I'm missing something.  After the last couple of posts by the author, it seems to me like data is being lost in the conversion to Excel.  Maybe I'm reading it wrong.

If data is not being lost in the conversion, then sdstuber's post is exactly correct.  You just need to change the format in Excel.
0
anumosesAuthor Commented:
I clearly mentioned that it is not oracle and the problem is happening in the output that is excel. I tried all formatting. Even HH24 and then formatting. No correct results so far.
0
johnsoneSenior Oracle DBACommented:
What type do you want the data to be in Excel?  Time or text?

If you want a time in Excel, then that is what you get.  Just like Oracle.  It has an internal storage method and a display option.  You cannot remove the seconds from the internal storage format, only the display format.

If you want text, then you need to change that during the import process.  You can try adding a single quote to the beginning of the field in the CSV file, but I'm not sure if Excel supports that during import.
0
anumosesAuthor Commented:
I understand. Once I upload 100 of drives, its a pain for me to remove that :00 from the website. Its time consuming. internal storage format is a problem as you said.
0
johnsoneSenior Oracle DBACommented:
Then why not change the type of the field to text during import?  You cannot sort it or treat it like time, but you will get the information they way you want it.
0
anumosesAuthor Commented:
Then why not change the type of the field to text during import?

In my above query can I change it?  or from excel spread sheet I have to format?
0
johnsoneSenior Oracle DBACommented:
You have to do it on the Excel side.  Excel is doing a conversion of the data coming in.
0
sdstuberCommented:
>>> In my above query can I change it?  

NO - you can't do anything from the Oracle side.  No matter how you format it in Oracle,  if the output looks like time, then Excel will interpret it as time and format the data itself.

>>> from excel spread sheet I have to format?

YES

Right-click on the column header, or select the cells you want to format if you don't want to format all of them.
Then click Format Cells
On the Number Tab, select "Time" in the Category list.
Then on the right, choose the format you want, which will probably be "1:30 PM"
Then click OK
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.