Oracle SQL help : formatting/parsing a 4 digit time (field is varchar)

Hi EE,

formatting a string that contains:  hhmm   for ex: 1700

Am looking to format military time:
     1)  to regular time.  If hh greater than 12, subtract  12
     2)  append AM/PM

Have to do this as part of a large SELECT SQL statement, to feed into another vendor's reporting tool.

Th SQL below works, BUT I want to wrap it with another CASE that it should only do this CASE if the field IS NOT NULL, other wise for NULL fields, IT SHOWS ': AM'

When I encase into another case that tests for NULL am getting a syntax error.

I commented out the outside CASE and it works.

If you uncomment it, get an error:  "missing keyword"


select

--CASE WHEN MEETING_TIME.BEGIN_TIME IS NULL THEN
  CASE
       WHEN TO_NUMBER(substr(MEETING_TIME.BEGIN_TIME,1,2)) > 12 THEN
                   TO_NUMBER(substr(MEETING_TIME.BEGIN_TIME,1,2)) - 12 || ':' ||
                             substr(MEETING_TIME.BEGIN_TIME,3,2) || ' PM'
                             
       ELSE
                   TO_NUMBER(substr(MEETING_TIME.BEGIN_TIME,1,2))|| ':' ||
                             substr(MEETING_TIME.BEGIN_TIME,3,2) || ' AM'
  END
--ELSE
--END    
       
from odsmgr.meeting_time



  screen shot
tx for your help, sandra
mytfeinAsked:
Who is Participating?
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.

slightwv (䄆 Netminder) Commented:
No need for a lot of code.

Try this:
select to_char(to_date('1700','HH24MI'),'HHMI AM') from dual;
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
mytfeinAuthor Commented:
Hi Slightwv,

wow...

thx for replying...

It worked and took care of not formatting the nulls

I added the comma to the date

I understand what you did.... first convert to a date and then to a format

thx so much, sandra

select to_char(to_date(MEETING_TIME.BEGIN_TIME,'HH24MI'),'HH:MI AM') from odsmgr.meeting_time
0
slightwv (䄆 Netminder) Commented:
Glad to help!
0
mytfeinAuthor Commented:
Dear Public,

I ended up concatenating the beg time, a hyphen, end time into one field like this

09:00 AM -   03:00 PM

Bec we are using a new report writer, and if I draw a hyphen label on the report then for a null beg time, the report writer would just show the hyphen.

In other words if i place 3 objects on the report:

  calc_begTime    -  (hyphen)         calc_EndTime

The hyphen is drawn as a label, so for null fields the report is showing just the hyphen.

So easier to control in the sql, and modified the to concatenate everything like this:

SELECT
   CASE WHEN MEETING_TIME.BEGIN_TIME IS NOT NULL
     THEN
       to_char(to_date(MEETING_TIME.BEGIN_TIME,'HH24MI'),'HH:MI AM') || ' - ' ||
       to_char(to_date(MEETING_TIME.END_TIME,'HH24MI'),'HH:MI AM')    
   END    
        AS Calc_MeetTime
       
FROM ODSMGR.MEETING_TIME
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.