Oracle sql - Decode Military time 1

[IngDep]

Currently have a passthrough query within MS Access that is used to extract data from an Oracle database.

Select Name, AdmitHr
from Encounter

Noticed that the query results for the "AdmitHr" field appears to be in military time format, i.e. 022, 17, 024, etc.

Can anyone offer insight as to how I can use the decode function to display the actual time - 2:00 pm instead of the military time?

Currently, using decode but thought that there is a more efficient method...

Thanks in advance.

 

[carp]

What you are calling "military time" is the format Oracle calls "HH24".
DECODE is probably inappropriate for this situation. If you are using a DATE datatype, just use TO_CHAR(my_date,'HH:MI:SS'). If this is a character string, then you would use something like:
TO_CHAR(TO_DATE(my_string,'HH24:MI:SS'),'HH:MI:SS')
.

 

[IngDep]

Did try both suggestions

Error message using DATE datatype is "Invalid number format model."
E.g.
TO_CHAR(CLP_ADM_HOUR_CD,'HH:MI:SS')


Error message using character string is "Invalid precision for decimal data type."

E.g.
TO_CHAR(TO_DATE(CLP_ADM_HOUR_CD,'HH24:MI:SS'),'HH:MI:SS')

 

[Dagon]

In the first one, there is no point applying a TO_CHAR function to something that is already a CHAR.

You'll need to explain exactly what "military format" is. For example, why is 22 preceded by a 0 but not 17 ? Is 24 really a valid time in this system ? Normally, times goes 23:59 and then 00:00, not 24:00. Do you have minutes and seconds in the format or is it always just hour ?

 

[carp]

Given your results (which would not occur with valid data inputs) as well as Dagon's spot-on questions (e.g., "why is 22 preceded by a 0"), please provide some actual examples of what you are seeing.

As several people (including myself) pointed out when you posted this question elsewhere, we really need either a more exact problem statement or actual data before we can help you.

At the very least, please tell us exactly what datatype you are working with - it makes a huge difference.

Also, if you are running this query in Access and not Oracle, then you might want to post this question on an Access forum instead of here.