Search code examples
sqloracle-databasedecimalhour

decimal to hour error 01850 in oracle sql

I have a column of hours in this format

HOUR
0 (meaning 0:00)
0.5 (meaning 0:30)
until 
23.5 (meaning 23:30)

I want to transform these decimals to real hours so I wrote the following code on pl sql

select TO_DATE (cast(hour as integer)||':'||round((cast(hour as int)-hour)*60,2),'HH24:MI') as f_hour
from half_hour

but now I'm getting ORA 01850: hour must be between 0 and 23 any help?

Solution

  • You are almost there. FLOOR will make sure, you get 23 for 23.5 in your table

    select TO_DATE (cast(FLOOR(hour) as integer)||':'||round((cast(hour as int)-hour)*60,2),'HH24:MI') as f_hour
from half_hour;

    CAST as INTEGER does rounding, 23.5 becomes 24.

    The above however, assumes, that you always have time in multiples of 30 minutes.

    I would have used something like below, which doesn't require time to be in multiples of 30 minutes

    select TO_DATE (FLOOR(hour) || ':'|| mod(hour,  floor(hour) )*60,'HH24:MI') as f_hour
    from half_hour;

    Explanation - 

    Input - 12.6
FLOOR(INPUT) - 12
MOD(INPUT, FLOOR(INPUT)) - 0.6
MOD(INPUT, FLOOR(INPUT))*60 - 36 <-- this is your minutes.