asked on

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

tx for your help, sandra

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mytfein

ASKER

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

slightwv (䄆 Netminder)

Glad to help!

mytfein

ASKER

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