Search code examples
sqloracleoracle-sqldeveloperdate-arithmetic

How to calculate hours from varchar2 fields?

I have two varchar2 date fields like clock_in and clock_out. I am inserting datetime by sysdate so the date looks like this:

Clock_In                   Clock_Out
12-28-13 08:00 AM          12-28-13 05:00 PM

Now I want to calculate the no of hours he work. Can you please help in this matter?

Solution

  • You could just convert the data to dates using the to_date function, and then subtract the two values. Since subtracting dates returns a difference in days, you can simply multiply by 24 to get the difference in hours:

    SELECT (TO_DATE(clock_out, 'DD-MM-YY HH:MI AM') - 
        TO_DATE(clock_in, 'DD-MM-YY HH:MI AM')) * 24 AS hours_worked
FROM   my_table

    EDIT:
    An even better solution would probably be to save clock_in and clock_out as date fields to begin with and avoid the hassle of converting in the query, but I'm not sure this is possible for the OP.