ORA-01850: hour must be between 0 and 23

[CHUMPY]

Hi,

I am trying to compare times only from date value i.e. independent of dates. I am trying to do this using the to_date function not sure if this is the best way..... but I come up against this error...... see block below

declare
test date;
begin
test:=TO_DATE(sysdate,'hh24:mi:ss');
dbms_output.put_line(test);
end;
/

which results in
ORA-01850: hour must be between 0 and 23
ORA-06512: at line 4

However works with a varchar2 i.e.

declare
test varchar2(100);
begin
test:=TO_char(sysdate,'hh24:mi:ss');
dbms_output.put_line(test);
end;
/

Is the to_char the only route?

Any ideas greatly appreciated,

Gareth

 

[karluk]

Yes, the second script is correct. The first script is trying to convert sysdate into a date. Since sysdate is already a date, the conversion fails.

There may be other ways to do this besides using the to_char function. However, you've found a way that works, so I would stick with it.

 

[SashaBuilder3]

I am new to Oracle, so my post may be inappropriate, yet you can try this.

If I clearly understood your task you want to compare time parts of some dates.

Basically, dates are numbers where the whole part represents days and the fraction -

time part.

In my procedures I use something like this:


Declare
full_date date;
time_part number;

begin
full_date := sysdate;
time_part := full_date - ROUND(full_date);

end;



Alexandre