Hello everyone,

What is the easiest and most efficient (is that possible? :-) ) way to calculate a time difference?

In a stored procedure, I need to do the following:

1) Read a value from a DateTime column
2) Get todays time (date and time)
3) subtract value of datetime column from todays date
4) see if the result of #3 is greater than some value (such as 15)

I want to work in minutes, not days. Does all of this make sense?

Thanks...

Scott

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

__________________
========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

will something like this work?

DECLARE @now datetime, @whateverdate datetime
SET @now = GETDATE()
set @whateverdate = '10/17/2007 15:00:00'

SELECT DATEPART(mi, @now - @whateverdate)


Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Hi Scott, a problem that you might have there is if, for example, a time like this was passed in: 10/17/2007 05:00 AM. If getdate() returned the value 10/17/2007 17:12:33 your above method would return 12, which is correct, 05:00 AM occured 12 hours and 12 minutes from the whatever date.

So the question is: are you concerened that the total difference of this computation is 732 minutes and not 12 hours and 12 minutes?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

Thanks for your response.
I need the computation in minutes, so if 732 minutes is returned, I am good with that. I am fairly certain that the date won't be coming in 10/17/2007 05:00 AM, but I will double check that.

Thanks again.

========================
Scott Klein
Author - Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

Hi Scott,
   Even if the date/time came in as 10/17/2007 05:00:00 you still wind up with a TimeSpan of 12 hours and 12 minutes (05:00 AM is just what i was playing around with in Query Analyzer).

In any event, this will give you a whole number of minutes passed between the 2 times:

DECLARE @now datetime, @whateverdate datetime
SET @now = GETDATE()
set @whateverdate = '10/17/2007 15:00:00'

SELECT DATEDIFF(mi, 0, @now - @whateverdate)

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

SELECT DATEDIFF(mi, @whateverdate, @now) % 1440

Hey peso, what is the purpose of % 1440 ? I am not familiar with that syntax.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

Peter... why the need for modulo here? If date difference is more than one day, that would hide it...

--Jeff Moden

% is the operator for Modulo... 1440 is the number of minutes in a day. Would show the number of minutes not considering number of days.

If time diff were 10 days and 60 minutes, would only show the 60 minutes.

--Jeff Moden

What a duh moment. Modulus operator, right. wow I need a vacation =
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========