EXACT NUMBER OF HOURS BETWEEN TWO DATES AND TIME (1 Viewer)

[georg0307]

Hi all,

I have the following query/sql that unfortunately doesn't calculate the exact number of hours between "Date1 Hour1" and "Date2 Hour2". If the result is under 24 hours it works but if the time that pass is over 25 26 27 28 hours it says too long to be displayed. How can adjust thais? Unfortunately in my Access TIMEDIFF doesn't exist.

SELECT [2021].Date, [2021].ORARIO_PORTINERIA, [2021].ORARIO_FINE_SCARICO, [2021].DATA_SDOGANAMENTO, [2021].ORARIO_SDOGANAMENTO, DateDiff("n",CDate(Format([Date] & " " & [ORARIO_PORTINERIA],"dd/mm/yyyy hh:nn")),CDate(Format([DATA_SDOGANAMENTO] & " " & [ORARIO_SDOGANAMENTO],"dd/mm/yyyy hh:nn")))/60 AS CUSTOM_TIME, DateDiff("n",CDate(Format([Date] & " " & [ORARIO_PORTINERIA],"dd/mm/yyyy hh:nn")),CDate(Format([Date] & " " & [ORARIO_FINE_SCARICO],"dd/mm/yyyy hh:nn")))/60 AS TEMPO_SCARICO
FROM 2021
ORDER BY [2021].Date DESC;


Thanks in advance

Georg

 

[ebs17]

SELECT
   [2021].[Date],
   [2021].ORARIO_PORTINERIA,
   [2021].ORARIO_FINE_SCARICO,
   [2021].DATA_SDOGANAMENTO,
   [2021].ORARIO_SDOGANAMENTO,
   DateDiff("h", [2021].[Date] + [2021].ORARIO_PORTINERIA ], [2021].[DATA_SDOGANAMENTO] + [2021].[ORARIO_SDOGANAMENTO]) AS CUSTOM_TIME,
   DateDiff("h", [2021].[Date] + [2021].[ORARIO_PORTINERIA], [2021].[Date] + [2021].[ORARIO_FINE_SCARICO]) AS TEMPO_SCARICO
FROM
   2021
ORDER BY
   [2021].[Date] DESC

 

[LarryE]

You might need to use the General Date format for DateDiff, because that format includes both Date and Time which would allow the calculation of date and time differences between dates no matter what time it is.

Format your dates in your table as General Date and that should help you. Then you can extract individual dates and times from the General Date format on forms and reports if you like.

 

[Gasman]

Why are you formatting a date to string and then converting back to date?

 

[The_Doc_Man]

You could also do this:

FIX(24*([datetime2] - [datetime1]))

Where datetime2 is the later date and datetime1 is the earlier date.

 

[Pat Hartman]

A datetime field is a "point in time" NOT an "elapsed time".

Make sure that the field you use to display the elapsed hours is defined as currency or single. The Currency data type (not to be confused with the currency format) eliminates the problem of floating point errors so it is a better choice.

 

[arnelgp]

copy this in a module:

' CHATGPT
Function TimeDifferenceInSeconds(StartTime As Date, EndTime As Date) As Long
    ' Calculate time difference in seconds
    If StartTime = EndTime Then
        TimeDifferenceInSeconds = 0
    Else
        TimeDifferenceInSeconds = DateDiff("s", StartTime, EndTime)
    End If
End Function

Function ConvertSecondsToHours(seconds As Long) As Double
    ' Convert seconds to hours
    ConvertSecondsToHours = seconds / 3600
End Function


Public Function TimeDiffInHours(ByVal StartTime As Date, ByVal EndTime As Date) As Double
    'Dim StartTime As Date
    'Dim EndTime As Date
    Dim timeDiffInSeconds As Long
    'Dim TimeDiffInHours As Double

    ' Set your start and end times
    'StartTime = #1/1/2024 10:00:00 AM#
    'EndTime = #1/1/2024 12:30:00 PM#

    ' Calculate time difference in seconds
    timeDiffInSeconds = TimeDifferenceInSeconds(StartTime, EndTime)

    ' Convert seconds to hours
    TimeDiffInHours = ConvertSecondsToHours(timeDiffInSeconds)

    ' Display the results
    'MsgBox "Time Difference in Seconds: " & timeDiffInSeconds & vbCrLf & _
    '       "Time Difference in Hours: " & timeDiffInHours
        

End Function

change your query to:

SELECT
    [2021].Date,
    [2021].ORARIO_PORTINERIA,
    [2021].ORARIO_FINE_SCARICO,
    [2021].DATA_SDOGANAMENTO,
    [2021].ORARIO_SDOGANAMENTO,
    TimeDiffInHours(CDate(Format([Date] & " " & [ORARIO_PORTINERIA],"mm/dd/yyyy hh:nn")),CDate(Format([DATA_SDOGANAMENTO] & " " & [ORARIO_SDOGANAMENTO],"mm/dd/yyyy hh:nn"))) AS CUSTOM_TIME,
    TimeDiffInHours(CDate(Format([Date] & " " & [ORARIO_PORTINERIA],"mm/dd/yyyy hh:nn")),CDate(Format([Date] & " " & [ORARIO_FINE_SCARICO],"mm/dd/yyyy hh:nn"))) AS TEMPO_SCARICO
FROM 2021
ORDER BY [2021].Date DESC;

 

[ebs17]

Why are you formatting a date to string and then converting back to date?

I ask myself the same thing. Effort, effort, effort. Three steps forward and three steps back.

Maybe because you can say that at least one thing is working properly - the processor.

 

[georg0307]

copy this in a module:

' CHATGPT
Function TimeDifferenceInSeconds(StartTime As Date, EndTime As Date) As Long
    ' Calculate time difference in seconds
    If StartTime = EndTime Then
        TimeDifferenceInSeconds = 0
    Else
        TimeDifferenceInSeconds = DateDiff("s", StartTime, EndTime)
    End If
End Function

Function ConvertSecondsToHours(seconds As Long) As Double
    ' Convert seconds to hours
    ConvertSecondsToHours = seconds / 3600
End Function


Public Function TimeDiffInHours(ByVal StartTime As Date, ByVal EndTime As Date) As Double
    'Dim StartTime As Date
    'Dim EndTime As Date
    Dim timeDiffInSeconds As Long
    'Dim TimeDiffInHours As Double

    ' Set your start and end times
    'StartTime = #1/1/2024 10:00:00 AM#
    'EndTime = #1/1/2024 12:30:00 PM#

    ' Calculate time difference in seconds
    timeDiffInSeconds = TimeDifferenceInSeconds(StartTime, EndTime)

    ' Convert seconds to hours
    TimeDiffInHours = ConvertSecondsToHours(timeDiffInSeconds)

    ' Display the results
    'MsgBox "Time Difference in Seconds: " & timeDiffInSeconds & vbCrLf & _
    '       "Time Difference in Hours: " & timeDiffInHours
       

End Function

change your query to:

SELECT
    [2021].Date,
    [2021].ORARIO_PORTINERIA,
    [2021].ORARIO_FINE_SCARICO,
    [2021].DATA_SDOGANAMENTO,
    [2021].ORARIO_SDOGANAMENTO,
    TimeDiffInHours(CDate(Format([Date] & " " & [ORARIO_PORTINERIA],"mm/dd/yyyy hh:nn")),CDate(Format([DATA_SDOGANAMENTO] & " " & [ORARIO_SDOGANAMENTO],"mm/dd/yyyy hh:nn"))) AS CUSTOM_TIME,
    TimeDiffInHours(CDate(Format([Date] & " " & [ORARIO_PORTINERIA],"mm/dd/yyyy hh:nn")),CDate(Format([Date] & " " & [ORARIO_FINE_SCARICO],"mm/dd/yyyy hh:nn"))) AS TEMPO_SCARICO
FROM 2021
ORDER BY [2021].Date DESC;

Ciao, thanks a lot for help. but I have to add the number of Days because it doesn' calculate over 24 hours, or may be I am doing something wrong. Georg

 

[Minty]

You are doing something wrong, start with the basics in the immediate window:

?datediff("h", now() ,date()-2)
-58

With hard coded values

?datediff("h", #2024-01-30 10:51:26# ,  #2024-01-28 11:30:05#)
-47

Both produce sensible answers.

 

[georg0307]

You are doing something wrong, start with the basics in the immediate window:

?datediff("h", now() ,date()-2)
-58

With hard coded values

?datediff("h", #2024-01-30 10:51:26# ,  #2024-01-28 11:30:05#)
-47

Both produce sensible answers.

Ciao, sorry I think, I don't understand.

 

[Minty]

What I was trying to explain, was that performing a very simple datediff() on dates more than 24 hours apart doesen't give an error, and will happily produce an answer over 24.

You can prove this yourself in the VBA editor in the immediate window. Open the editor and press CTRL+G to make it visible.
Type the following and hit enter
? Now()
The ? is shorthand for Print

So with some simple experiments you can work out what you need to pass to your expression to get the desired results.

 

[arnelgp]

see attachment db on post #11 for some demo.
Employee Timesheet | Access World Forums (access-programmers.co.uk)

 

[The_Doc_Man]

I suspect that part of this problem is that you misunderstand what Access is doing to you. IF you use the "standard" Access FORMAT function for dates and times, the real problem is that you cannot translate a time greater that 24 hours because the formatting routines won't allow that. Oh, how I wish that Access had a formatting template that would allow you to ask for "hhhhh:nn:ss" - but it doesn't.

You absolutely CAN compute the difference between two times that are more than 24 hours apart just by subtracting one from the other using a DATE variable to hold the result. But you can't display it using standard FORMAT function specifications. To make this work, you must compute the difference in ONE step, and then convert that difference to another type of variable before you display the result. That is why folks are pointing you to the DATEDIFF function or some other method of conversion.