DateDiff function (1 Viewer)

[radek225]

I try to use datediff function with format() to count difference between now() and starting time my activity -tblProcesyZleceniaSzczegoly.datarozpoczecia

I want to use format 00h and 00min

e.g.
now - 19:06
starting activity time 20:00
result shoudl be 00h and 54min
but when I try to use datediif ms access shows me 1 hour as a results:/

e.g.
now - Thursday 11:10
starting activity - Monday 11:00

results shoudl be 72h and 10min
How should I create my sql code?

 

[CJ_London]

How should I create my sql code?

How have you created it at the moment? No point in suggesting what you have already tried

 

[MarkK]

Hey radek,
DateDiff() allows you to specify different intervals of time on which to calculate a difference. I would expect that if you used "hours" as the interval . . .

? datediff("h", #19:06#, #20:00#)

. . . then the result would be 1. But try using minutes, which is represented by an "n" so as not to confuse it with months . . .

? datediff("n", #19:06#, #20:00#)

. . . and see if you get different results.

 

[radek225]

Hey radek,
DateDiff() allows you to specify different intervals of time on which to calculate a difference. I would expect that if you used "hours" as the interval . . .

? datediff("h", #19:06#, #20:00#)

. . . then the result would be 1. But try using minutes, which is represented by an "n" so as not to confuse it with months . . .

? datediff("n", #19:06#, #20:00#)

. . . and see if you get different results.

Thanks Markk for your insight. The problem is, that I should get from my query 'n' but limit should be 60. Yes I tried to combinated h and n. if you have less than 60minutes difference, then it's ok, but when we have e.g. 70min? then results should be 1h and 10min not 0:70min

 

[radek225]

How have you created it at the moment? No point in suggesting what you have already tried

Format what I need is

e.g.

SELECT Format(Now(),"h") & "h and " & Format(Now(),"n") & "min" AS Wyr1;

it's just example

Part of my code

SELECT datediff('h', tblProcesyZleceniaSzczegoly.datarozpoczecia, now()) as czas from tblProcesyZleceniaSzczegoly where identyfikator =1;

 

[MarkK]

Does this help?

Function GetElapsedTime(t1 As Date, t2 As Date) As Date
    Dim minutes As Long
    
    minutes = Abs(DateDiff("n", t1, t2))       [COLOR="Green"]'calculate minutes difference[/COLOR]
    GetElapsedTime = TimeSerial(0, minutes, 0) [COLOR="Green"]'convert minutes back to valid date/time[/COLOR]
End Function

Here we calculate the minutes using DateDiff(), but we convert that result back to a valid Date/Time variable in VBA.

 

[radek225]

Does this help?

Function GetElapsedTime(t1 As Date, t2 As Date) As Date
    Dim minutes As Long
    
    minutes = Abs(DateDiff("n", t1, t2))       [COLOR="Green"]'calculate minutes difference[/COLOR]
    GetElapsedTime = TimeSerial(0, minutes, 0) [COLOR="Green"]'convert minutes back to valid date/time[/COLOR]
End Function

Here we calculate the minutes using DateDiff(), but we convert that result back to a valid Date/Time variable in VBA.

Almost!

Result is exactly what I expect BUT I need to use it in sql query:/

 

[MarkK]

TimeDiffField: TimeSerial(0, Abs(DateDiff("n", field1, field2)), 0)

or can't you just do a subtraction on the date values to get the difference, like . . .

TimeDiffField: cdate(abs(field1 - field2))

 

[arnelgp]

you can also use:

SELECT (((DateDiff("n", tblProcesyZleceniaSzczegoly.datarozpoczecia, Now()) \ 60)) & Format((DateDiff("n", tblProcesyZleceniaSzczegoly.datarozpoczecia, Now()) Mod 60), "\:00")) as czas from tblProcesyZleceniaSzczegoly where identyfikator =1;