?
Solved

Getting total minutes from two date fields.

Posted on 2008-02-11
5
Medium Priority
?
149 Views
Last Modified: 2010-04-21
Hello Experts,

I have the following Select Statement:


SELECT tblOutage.Case_ID, AST_OutageLog.OutageStartDate, AST_OutageLog.OutageEndDate, Format([AST_OutageLog.OutageEndDate]-[AST_OutageLog.OutageStartDate]) AS OutageMinutes, tblOutage.Assigned_To_Group, tblOutage.Priority, tblOutage.Status, tblOutage.CreateTime, tblOutage.ResolvedTime, tblOutage.OutageFlag, tblOutage.Summary, tblOutage.Package
FROM tblOutage INNER JOIN AST_OutageLog ON tblOutage.Case_ID = AST_OutageLog.Case_ID
WHERE Some conditions;

Within this statement I need to get the outage minutes which is the difference between OutageEndDate - OutageStartDate. I have tried using format as you can see but this not returning what I expected.

OutageStartDate = 2/8/2008 7:00:00 AM
OutageEndDate = 2/8/2008 9:34:00 AM
OutageMinutes = 154 minutes
The query is returning 0.106944444443798      

Any Ideas?
0
Comment
Question by:eddiepardon
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20867392
try this

SELECT tblOutage.Case_ID, AST_OutageLog.OutageStartDate, AST_OutageLog.OutageEndDate, datediff('n',[AST_OutageLog.OutageEndDate],[AST_OutageLog.OutageStartDate]) AS OutageMinutes, tblOutage.Assigned_To_Group, tblOutage.Priority, tblOutage.Status, tblOutage.CreateTime, tblOutage.ResolvedTime, tblOutage.OutageFlag, tblOutage.Summary, tblOutage.Package
FROM tblOutage INNER JOIN AST_OutageLog ON tblOutage.Case_ID = AST_OutageLog.Case_ID
WHERE Some conditions;
0
 

Author Comment

by:eddiepardon
ID: 20867443
This works but it displays it as a neg number -154. How can I make it show just 154?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20867444
use
DATEDIFF(mi, OutageStartDate ,OutageEndDate  )
0
 
LVL 26

Accepted Solution

by:
ee_rlee earned 2000 total points
ID: 20867462
SELECT tblOutage.Case_ID, AST_OutageLog.OutageStartDate, AST_OutageLog.OutageEndDate, datediff('n',[AST_OutageLog.OutageStartDate],[AST_OutageLog.OutageEndDate]) AS OutageMinutes, tblOutage.Assigned_To_Group, tblOutage.Priority, tblOutage.Status, tblOutage.CreateTime, tblOutage.ResolvedTime, tblOutage.OutageFlag, tblOutage.Summary, tblOutage.Package
FROM tblOutage INNER JOIN AST_OutageLog ON tblOutage.Case_ID = AST_OutageLog.Case_ID
WHERE Some conditions;
0
 

Author Closing Comment

by:eddiepardon
ID: 31429828
Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Suggested Courses

588 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