subtract datetime value from null

Hello I have a table in which there are two fields actual_time_from, actual_time_to. It stores sign on and sign off details. Null is inserted if actual time to extends past 23:59. How do I find hours worked if actual time to is null. I need to do something like select actual_time_to - actual_time_from as work_hours from t1; If I try using nvl(actual_time_to, to_date('24:00', '%H:%M')) it gives error -1263 A field in a datetime or interval is out of range, incorrect, or missing. If I try using nvl(actual_time_to, to_date('01 00:00', '%d %H:%M')) for fields where it is not null; 31 is put in day field. This leads to incorrect subtraction when actual_time_from is not null and actual_time_to is null. -30 06:00 is the output in such case when instead I want it to output 18:00 when actual_time_from is "06:00" and actual_time_to is null. If I put nvl(actual_time_to, to_date('24:00', '%H:%M')), difference evaluates to 17:59 in previous case which is not correct logically. Things work only if both are null. But in that case, I can leave it blank. I can do the calculation by taking it in java but for 1 specific report I need to happen through sql may be through temporary tables but using sql only.

Sorry the line with output calculated as 17:59 should have read nvl(actual_time_to, to_date('23:59', '%H:%M'))

On Fri, Aug 31, 2012 at 6:05 AM, PRAJAKTA CHIKHALE <prajakta_s_c@yahoo.co.in > wrote: > Hello I have a table in which there are two fields actual_time_from, > actual_time_to. It stores sign on and sign off details. Null is inserted if > actual time to extends past 23:59. > > How do I find hours worked if actual time to is null. > > I need to do something like > select actual_time_to - actual_time_from as work_hours from t1; > > If I try using nvl(actual_time_to, to_date('24:00', '%H:%M')) it gives > error > -1263 A field in a datetime or interval is out of range, incorrect, or > missing. > > If I try using nvl(actual_time_to, to_date('01 00:00', '%d %H:%M')) for > fields > where it is not null; 31 is put in day field. This leads to incorrect > subtraction when actual_time_from is not null and actual_time_to is null. > > -30 06:00 is the output in such case when instead I want it to output 18:00 > when actual_time_from is "06:00" and actual_time_to is null. > > If I put nvl(actual_time_to, to_date('24:00', '%H:%M')), difference > evaluates > to 17:59 in previous case which is not correct logically. > > Things work only if both are null. But in that case, I can leave it blank. > Typo for 'if both are not null'? > I can do the calculation by taking it in java but for 1 specific report I > need > to happen through sql may be through temporary tables but using sql only. > With addendum: Sorry the line with output calculated as 17:59 should have read > nvl(actual_time_to, to_date('23:59', '%H:%M')) > It may not be obvious (it probably isn't obvious), but it is often easiest to do time calculations when the type is INTERVAL rather than DATETIME HOUR TO MINUTE (in this case, DATETIME HOUR TO SECOND, etc). In this case, I'd probably look to convert the times by subtracting DATETIME(00:00) HOUR TO MINUTE from the times you've got stored. Note, in particular, that INTERVAL(24:00) HOUR TO MINUTE is a very respectable value. To calculate the end time: NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) To calculate the start time: (actual_time_from - DATETIME(00:00) HOUR TO MINUTE) So the time taken is: NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) - (actual_time_from - DATETIME(00:00) HOUR TO MINUTE) Which is a mouthful, but effective. Test case: CREATE TABLE WorkTimes ( actual_time_from DATETIME HOUR TO MINUTE NOT NULL, actual_time_to DATETIME HOUR TO MINUTE ); INSERT INTO WorkTimes VALUES('09:00', '17:00'); INSERT INTO WorkTimes VALUES('13:00', NULL); SELECT Actual_time_to, actual_time_from, (actual_time_from - DATETIME(00:00) HOUR TO MINUTE) fr_interval, NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) AS to_interval, NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) - (actual_time_from - DATETIME(00:00) HOUR TO MINUTE) AS time_worked FROM WorkTimes ORDER BY actual_time_from; 17:00 09:00 9:00 17:00 8:00 13:00 13:00 24:00 11:00 The output looks better in fixed-width font, of course. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04083aa12feb7604c890a39f

Thanks for your great help. I was able to solve the problem through single sql. Well, I am not trying to be rude but both are null was not typo. If employee is on leave or rest, a record is inserted in this table with both times as null. Here with sql using to_date and nvl changed the values as 01 00:00 and 01 00:00 and subtraction was 0 00:00 which was correct. Also I had other requirement for which I could obtain solution from your sql. I am posting it here so that in future if anyone requires the same (s)he can use it. If employee works from 22:00 hours of one day to 08:00 hours of next day, for first day he would enter 22:00 in actual_time_from and null in actual_time_to. For next day he would enter null in actual_time_from and 08:00 hours in actual_time_to. Solution : select actual_time_to, actual_time_from, NVL(actual_time_from - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) AS fr_interval, NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) AS to_interval, NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) - NVL(actual_time_from - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR TO MINUTE) AS time_worked FROM WorkTimes;