0

Forgive me if this is discussed somewhere else, and I just didn't know how to ask the question. My issue is; How to calculate a time interval greater than 24 hours. I have a formula comprised of 2 other formulas that looks like this:

    Time({%EMS_Departed_DateTime}) - Time ({%Pt_Arrival_DateTme})

This was created, because we had an issue with Dates and Times being stored as separate values in the database. The SQL expressions look like this:

    CAST(
         CONVERT(Varchar(10),PT_ArrivedAtED,112)+ ' '  +
         CONVERT(Varchar(8),PT_ArrivedAtED_Time) AS DateTime)

What I need is a calculation of a time interval represented as:

HH:mm:ss

I did some research and found this:

    WhilePrintingRecords; 
    NumberVar TotalSec :=({@LengthofStay_Time}); 
    NumberVar Days    := Truncate  (TotalSec / 86400); 
    NumberVar Hours   := Truncate  (Remainder ( TotalSec , 86400) / 3600) ; 
    NumberVar Minutes := Truncate  (Remainder ( TotalSec , 3600) / 60) ; 
    NumberVar Seconds := Remainder (TotalSec , 60) ; 

    Totext (Days ,     '00' ) +  ':' +
    Totext ( Hours ,   '00' ) +  ':' + 
    Totext ( Minutes , '00' ) +  ':' + 
    Totext ( Seconds , '00' )

This almost works. What I see after all of this is calculated and converted to display the time as demonstrated above is hours, minutes, and seconds. It will not calculate a number of days (or, preferably a total number of hours (i.e. 2 days = 48 hours), minutes, and seconds. I am looking for advice/solution as to how I should write a formula or SQL expression that would calculate the time interval from {%Pt_Arrival_DateTme} to {%EMS_Departed_DateTime} displayed as HH:mm:ss. Thank you in advance for your guidance and advice.

Retrowbridge
  • 3
  • 4
  • I think I failed to mention that the final formula does show the result of the calculation, but only as hours, minutes, and seconds not exceeding 24 hours. I.e. an interval which should be displayed as 31:25:42 is displayed as 7:25:42, because it will not exceed 24 hours. I would be happy even if it displayed as 01:07:25:42 (dd:hh:mm:ss) – Retrowbridge Feb 11 '16 at 19:49

2 Answers2

0

Crystal

   WhilePrintingRecords; 
    NumberVar TotalSec :=(60*60)*2; 
    NumberVar Hours   := Truncate  (Remainder ( TotalSec , 86400) / 60) ; 
    NumberVar Minutes := Truncate  (Remainder ( TotalSec , 3600) / 60) ; 
    NumberVar Seconds := Remainder (TotalSec , 60) ; 

    Totext ( Hours ,   '00' ) +  ':' + 
    Totext ( Minutes , '00' ) +  ':' + 
    Totext ( Seconds , '00' )

SQL

https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql

SQL server, Converting Seconds to Minutes, Hours, Days

Community
  • 1
  • 1
CoSpringsGuy
  • 1,615
  • 1
  • 13
  • 16
0

Try my ElapsedTime() function:

// Convert seconds to a string in the format DD.HH:MM:SS
Function (NumberVar interval)

  NumberVar Days := Truncate(interval / 86400);
  NumberVar Hours := Truncate(Remainder(interval, 86400) / 3600);
  NumberVar Minutes := Truncate(Remainder(interval, 3600) / 60);
  NumberVar Seconds := Remainder(interval, 60);
  Totext(Days,'##') +'.'+ Totext(Hours,'00') +':'+ Totext(Minutes,'00') +':'+ Totext(Seconds,'00')

Usage: ElapsedTime ((24*60*60)+60)

Results: 1.00:01:00

** edit **

Try ElapsedTime( {%EMS_Departed_DateTime} - {%Pt_Arrival_DateTme} )

Community
  • 1
  • 1
craig
  • 25,664
  • 27
  • 119
  • 205
  • That didn't work, but I think I know why. I think that my interval is incorrect. My interval formula is: ` Time({%EMS_Departed_DateTime}) Time({%Pt_Arrival_DateTme})` Is it possible that the **Time** outside the parenthesis should be something different? – Retrowbridge Feb 17 '16 at 14:08
  • I was just thinking that I actually need this in elapsed **HH:mm:ss** – Retrowbridge Feb 17 '16 at 14:29