Difference between two date and times 2

[gromboy]

Hi,
I have an sql server db and want to calculate the difference between two time and dates, in hours.

Field 1: {PHARMCHECKDATE} Datetime field (with no time entered)
Field 2: {PHARMCHECKTIME} String field


Field 3: {PRESC_DATE} Datetime field (with no time entered)
Field 4: {PRESC_TIME) String field


What I need to do is get the hours difference between - ({PRESC_DATE}{PRESC_TIME))-({PHARMCHECKDATE {PHARMCHECKTIME})

In the front end of the app..a user will do a Pharmacy check...then a day or few hours after the Prescriber will "Authorise" treatment. But it sometimes doesnt happen in this order...

I need the time difference between both... whether this is + or -
Any help grately appreciated as ever
Steve

 

[IanWaterman]

What is the format of you Time String

Basically you need to create datetime strings, so that you can use the function

DTSToDateTime ("2000/01/13 11:30:15")

Assuming your dates and time as as above

@TimeDiff
datetimevar Presc;
datetimevar Pharm;

Presc:= dtstoDatetime(toext({PRESC_DATE}, 'yyyy/MM/dd')&" "&{PRESC_TIME});

Pharm:= dtstoDatetime(toext({PHARMCHECKDATE}, 'yyyy/MM/dd')&" "&{PHARMCHECKTIME});

datediff("h", Presc, Pharm):

Ian

 

[gromboy]

Ian...that works, thanks so much
Best Wishes
Steve

 

[gromboy]

Hi,
Continuation from the above thread back in October...

I've discovered I need the hours and minutes from this formula rather than hours only.... I've tried making changes but havent been able to do it..

This is the formula..

@TimeDiff
datetimevar Presc;
datetimevar Pharm;

Presc:= dtstoDatetime(toext({PRESC_DATE}, 'yyyy/MM/dd')&" "&{PRESC_TIME});

Pharm:= dtstoDatetime(toext({PHARMCHECKDATE}, 'yyyy/MM/dd')&" "&{PHARMCHECKTIME});

datediff("h", Presc, Pharm):

Any help greatly appreciated as ever
Steve

 

[IanWaterman]

@TimeDiff
datetimevar Presc;
datetimevar Pharm;
numbervar timediff;
numbervar hour;
numbervar minutes;


Presc:= dtstoDatetime(toext({PRESC_DATE}, 'yyyy/MM/dd')&" "&{PRESC_TIME});

Pharm:= dtstoDatetime(toext({PHARMCHECKDATE}, 'yyyy/MM/dd')&" "&{PHARMCHECKTIME});

timediff:= datediff("n", Presc, Pharm);

hour:= int(timediff/60);
min:= remainder(timediff, 60);

Ian

 

[gromboy]

Thanks Ian...
Seem to be getting an error here

min:= remainder(timediff, 60);

Remaining text does not appear to be part of the formula
Steve

 

[IanWaterman]

Sorry declared var and var used are different!

Should be

minutes:= remainder(timediff, 60);

 

[gromboy]

It seems to work for the minutes side of things but when its over one day I need it to display hours:minutes

For example
14/11/2008 14:19 vs 17/11/2008 12:42 is outputting 23

Over one hour also seem a bit odd..

can we display HH:MM ?

Steve

 

[gromboy]

I guess we need to take the "n" from here

timediff:= datediff("n", Presc, Pharm);

and express this as a HH:MM:SS.. Not sure how to do this..

I've had a play... but no joy
Steve

 

[IanWaterman]

Just convert your numbervars to text.

Last line of formula should be

totext(hour,0,"")&":"& totext(minutes,0,"")

Ian

 

[gromboy]

My formula now reads:

datetimevar Presc;
datetimevar Pharm;
numbervar timediff;
numbervar hour;
numbervar minutes;


Presc:= dtstoDatetime(totext({PATDRUG.PRESC_DATE}, 'yyyy/MM/dd')&" "&{PATDRUG.PRESC_TIME});

Pharm:= dtstoDatetime(totext({PATDRUG.PHARMCHECKDATE}, 'yyyy/MM/dd')&" "&{PATDRUG.PHARMCHECKTIME});
minutes:= remainder(timediff, 60);
totext(hour,0,"")&":"& totext(minutes,0,"")


But I get 00:00 for every output
Steve

 

[lbass]

You forgot to set the value of the variable timediff.

-LB

 

[gromboy]

Sorry.. I'm a bit thick...
What exactly do I need to do ?
Steve

 

[IanWaterman]

As LB said you ahve left out the evalauation of key vars

timediff:= datediff("n", Presc, Pharm);
hour:= int(timediff/60);

YOur formula should be

datetimevar Presc;
datetimevar Pharm;
numbervar timediff;
numbervar hour;
numbervar minutes;


Presc:= dtstoDatetime(totext({PATDRUG.PRESC_DATE}, 'yyyy/MM/dd')&" "&{PATDRUG.PRESC_TIME});

Pharm:= dtstoDatetime(totext({PATDRUG.PHARMCHECKDATE}, 'yyyy/MM/dd')&" "&{PATDRUG.PHARMCHECKTIME});

timediff:= datediff("n", Presc, Pharm);
hour:= int(timediff/60);

minutes:= remainder(timediff, 60);
totext(hour,0,"")&":"& totext(minutes,0,"")

Ian

 

[gromboy]

Hi Ian,
It works great apart from when I have less than 10 minutes between times... It then puts the value in the wrong place.

eg 7 minutes represented as 00:7 with no trailing zero...

Steve

 

[lbass]

Change the last line to:

totext(hour,0,"")&":"& totext(minutes,"00")

-LB

 

[gromboy]

Thankyou to you both... this works great..
best wishes
Steve