Adding hours with %TIME function not working when result goes over midnight

[64waves]

Good afternoon,

I have a program that users scan a worker that is working through his lunch. My program takes the start time and adds 4 hours to a field, then adds 6 hours to a field. When the start time of a person is 18:00:00, the first part of the works, giving a time of 22:00:00. But when I try to take their start time and add 6 hours to it: 18:00:00 + 6 hours, the result is zero. TSTART is 1800.
Any idea why this is not working?

eval Fullstart = %DEC((%char(Tstart) + '00'):6:0)
eval Fstarttime= %char(%time(Fullstart):*HMS)

eval strwindow =%Dec((%time(Fstarttime:*HMS) +
%hours(4)):*HMS)
eval endwindow =%Dec((%time(Fstarttime:*HMS) +
%hours(6)):*HMS)

Thanks in advance.

[64waves]

I think I just realized what is going on, it is setting the time to 00:00:00, when I want 24:00:00. Interesting.

[Jim_IT]

If you want results from 1000 to 2400 then another way to do this is:

[FONT=trebuchet ms]exec sql
  values
    Coalesce(Nullif(Mod(TSTART+400,2400),0),2400)
  into :strwindow;
exec sql
  values
    Coalesce(Nullif(Mod(TSTART+600,2400),0),2400)
  into :endwindow;[/FONT]

You can see how it works in Run Sql Scripts:

[FONT=trebuchet ms]Select
TSTART,
Coalesce(Nullif(Mod(TSTART+600,2400),0),2400)
From
(values 
(2400),(0100),(0200),(0300),(0400),(0500),(0600),(0700),
(0800),(0900),(1000),(1100),(1200),(1300),(1400),(1500),
(1600),(1700),(1800),(1900),(2000),(2100),(2200),(2300))
As a (TSTART)[/FONT]

Jim

[Jim_IT]

dang, still can't edit my own posts. Was supposed to be 0100 - 2400.

[JonBoy]

I think I just realized what is going on, it is setting the time to 00:00:00, when I want 24:00:00. Interesting.

Easiest way to handle this would be to do this:

If date = *LoVal;
date = *HiVal;
Endif;

[Scott Klement]

I must admit, I don't really understand the problem. Whether represented as 24:00:00 or 0:00:00 -- they both mean midnight... so why does it matter which is shown? Though, I must admit, I have not seen an application that shows "24:00:00", that does seem like a very strange time... but, in this case, I guess it's what the user wants?

[Whitecat27]

I must admit, I don't really understand the problem. Whether represented as 24:00:00 or 0:00:00 -- they both mean midnight... so why does it matter which is shown? Though, I must admit, I have not seen an application that shows "24:00:00", that does seem like a very strange time... but, in this case, I guess it's what the user wants?

In a strict interpretation of a day by using Military Time, "24:00:00" refers to the previous day, while "00:00:00" refers to the next day. The exact midnight hour is actually a special case of something that can be either today's midnight or tomorrow's midnight. While it is true that both representations of that exact time are equal to each other, the written version of midnight sometimes needs to have a further precision to identify what day is being referenced.

Best Regards,
Fred Williams

[Rocky]

So - what you're saying Fred is it's a matter of reference - the day ends at 24:00:00 - while the day begins at 00:00:00.

[tomliotta]

What are the data types of the relevant variables? Which of those variables would need actually to show a "24:00" value? (Or exactly what value needs to be shown?) Is "24:00" guaranteed to be the absolute maximum possible value?

Perhaps part of the problem is that there is a series of functions and conversions between data types. Even if exactly "24:00" is an intermediate result, maybe conversion somewhere ends up at "00:00".

[JonBoy]

There's nothing mystical going on Tom or indeed any intermediate results causing issues.

This is just the way the underlying system support works. I _believe_ the only time that 24:00 comes into play is when it is a stored value or *HiVal is used. A *HiVal of 24:00 makes sense because it will always higher than any value that can arise from calculations.

[Whitecat27]

So - what you're saying Fred is it's a matter of reference - the day ends at 24:00:00 - while the day begins at 00:00:00.

Rocky,

Yes, that's a wonderfully correct interpretation of what I meant.


Best Regards,
Fred Williams

[64waves]

Good morning all,

thank you for all the insight. What I am ultimately trying to do is compare when a person gets scanned, that time in HH:MM:SS is compared to a start window (start time + 4 hrs) and an endwindow (start time + 6 hours). When a person that starts at 18:00:00 gets scanned the endwindow was being calculated to 00:00:00. For my comparison I need it to be 24:00:00 so I can see if my scan time is between the start and end windows.

JonBoy's reply seems to be the most concise, I just put in if the start time is 18:00 and endwindow is 00:00:00, endwindow = 24:00:00.

Thanks again for all the insight.

Have a good day.

[Rocky]

Greetings,

Sounds like to me a timestamp rather than a date field would be more appropriate - as that deals with the issue around midnight.

Food for thought.

[64waves]

Thanks Rocky, I'll keep that in mind.

Have a good day.