We help IT Professionals succeed at work.

How to calculate number of minutes between two DateTime in a query?

Stef Merlijn
Stef Merlijn asked
on
Medium Priority
675 Views
Last Modified: 2010-04-05
Hi,

In Delphi I can calculate the number of minutes between two dates by using the function:
MinutesBetweenEx(StartDateTime, EndDateTime);

Is there a way to do this within a query?
I'm using a MS Access database.

Regards,
Stef
Comment
Watch Question

Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008
Commented:
this comes from MSSQL try maybr it will work on Access
DATEDIFF(mi, <startdate>, <enddate>)

ziolko.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mike LittlewoodEngineer

Commented:
Assuming MS Access has the function DATEDIFF

SELECT DateDiff( mi, <STARTDATE>, <ENDDATE>) AS MinutesBetween
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
hi, mike
looks like youre few seconds later:)
ziolko.
I guess it should be :
SELECT DateDiff( "n", <STARTDATE>, <ENDDATE>) AS MinutesBetween

instead of

SELECT DateDiff( mi, <STARTDATE>, <ENDDATE>) AS MinutesBetween

Best regrads,

The Mayor
And use :

For Hours    : SELECT DateDiff( "h", <STARTDATE>, <ENDDATE>) AS HoursBetween
For Minutes : SELECT DateDiff( "n", <STARTDATE>, <ENDDATE>) AS MinutesBetween
For seconds : SELECT DateDiff( "s", <STARTDATE>, <ENDDATE>) AS SecondsBetween

See http://www.access-programmers.co.uk/forums/showthread.php?t=14680

Mike LittlewoodEngineer

Commented:
Yeah looks like we posted seconds apart Ziolko   :o)
Little Extention to what you were asking :

SELECT FromDate, ToDate,
       DATEDIFF( "s", FromDate, ToDate) as TotalSeconds,
       INT(TotalSeconds / 3600) as Hours,
       INT( ((TotalSeconds / 3600) - Hours) *60 ) as Minutes,
       INT( ((((TotalSeconds / 3600) - Hours) *60) - Minutes) *60 ) as Seconds
from YourTableNameHere

Best regards,

The Mayor.
Stef MerlijnDeveloper

Author

Commented:
Thank you both very much for the solution.
I never knew that you can use the "as"-value within the statement.

Regards,
Stef
Glad to be of some help.
And thanks for the points and the grade.

Best regards,

The Mayor.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.