Formatting DateDiff hh:nn:ss

[jpl458]

I have the following expression in a query in the QBE grid:

Dur:  Format ( DateDiff ("s", [TimeValue(dteCreated]), [TimeValue(dteModified]),  hh:nn:ss" ) AS HH_NN_SS

Trying to calc the duration between 2 times;

Get this error

It must be something I am overlooking, but I can't see it.

 

[theDBguy]

Did you forget the opening quotes for the second Format argument?

Also, don't think you need the "AS" part.

 

[plog]

4 left parenthesis, 3 right.

 

[ebs17]

The process is unclear. Seconds (from the difference) cannot be converted directly into the format used.

Format(dteModified - dteCreated, "hh:nn:ss")

... shows times less than 24 hours.

 

[theDBguy]

The process is unclear. Seconds (from the difference) cannot be converted directly into the format used.

Format(dteModified - dteCreated, "hh:nn:ss")

... shows times less than 24 hours.

I agree. Perhaps the OP is trying to use something like this.

Date/Time: Calculate differences in time

 

[Pat Hartman]

Your parenthes and square brackets are mixed up.

Dur: Format ( DateDiff ("s", [TimeValue(dteCreated]), [TimeValue(dteModified]), hh:nn:ss" ) AS HH_NN_SS

Dur: Format ( DateDiff ("s", TimeValue([dteCreated]), TimeValue([dteModified]), "hh:nn:ss" ) AS HH_NN_SS

That is syntactically correct but it doesn't work since the datediff() won't return a formatted string. It returns seconds as you have used it. You need to create your own custom function which takes in seconds and returns a string formatted as hh:nn:ss

 

[ebs17]

... whereby the question arises why one should take a detour via seconds if one wants to have an entry in hh:nn:ss.

 

[Pat Hartman]

The OP is calculating the difference between two times. The DateDiff() does NOT return the result in the desired format. That is why.