DateDiff using SQL Query

Posted on 2011-10-09
Last Modified: 2012-05-12
Hi all,

I have a table which has 2 columns Start Time and End Time both columns are formatted as Date/Time Short Time. i.e

Start Time    End Time   Duration
00:00           00:00        00:00

I would like to use the columns to calculate the Duration column however I am not having much success. I am trying to use a SQL Query which looks like the below:

SELECT DATEDIFF([StartTime],[EndTime]) as Duration FROM [DailyTaskLog]

I am sure that I have use something simular to this and it has work however I am having a complete mind blank and am not sure what I am doing wrong.

Kind Regards
Question by:SerinaStar
    LVL 75

    Accepted Solution

    You need to specify an Interval .... example Minutes below (n).

    SELECT DATEDIFF( "n", [StartTime],[EndTime]) as Duration FROM [DailyTaskLog]
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    Interval Settings (from Help)

    yyyy Year
    q Quarter
    m Month
    y Day of year
    d Day
    w Weekday
    ww Week
    h Hour
    n Minute
    s Second
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    To get the number of minutes between the two times, use:

    SELECT DateDiff("n", [StartTime], [EndTime]) as Duration-Min FROM [DailyTaskLog]

    LVL 12

    Assisted Solution

    In addtion to correct statement of DatabaseMX, if you want a more complete DateDiff function, check below thread out:

    Hope this helps,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now