?
Solved

DateDiff using SQL Query

Posted on 2011-10-09
4
Medium Priority
?
308 Views
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
SS
0
Comment
Question by:SerinaStar
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1332 total points
ID: 36940321
You need to specify an Interval .... example Minutes below (n).


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

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1332 total points
ID: 36940323
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
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36940335
To get the number of minutes between the two times, use:

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

0
 
LVL 12

Assisted Solution

by:danishani
danishani earned 668 total points
ID: 36940336
In addtion to correct statement of DatabaseMX, if you want a more complete DateDiff function, check below thread out:
http://www.accessmvp.com/djsteele/Diff2Dates.html

Hope this helps,
Daniel
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

862 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