Wrox Programmer Forums Calculating a time difference
 |
 SQL Server 2005 General discussion of SQL Server *2005* version only.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

October 17th, 2007, 03:35 PM
 SQLScott Wrox Author Join Date: Dec 2004 Posts: 338 Thanks: 0 Thanked 2 Times in 2 Posts
Calculating a time difference

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
========================

October 17th, 2007, 03:56 PM
 SQLScott Wrox Author Join Date: Dec 2004 Posts: 338 Thanks: 0 Thanked 2 Times in 2 Posts

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

October 17th, 2007, 04:20 PM
 dparsons Wrox Author Join Date: Oct 2005 Posts: 4,104 Thanks: 1 Thanked 64 Times in 64 Posts

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?

================================================== =========
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
================================================== =========

October 17th, 2007, 06:53 PM
 SQLScott Wrox Author Join Date: Dec 2004 Posts: 338 Thanks: 0 Thanked 2 Times in 2 Posts

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
========================

October 17th, 2007, 07:58 PM
 dparsons Wrox Author Join Date: Oct 2005 Posts: 4,104 Thanks: 1 Thanked 64 Times in 64 Posts

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

================================================== =========
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
================================================== =========

October 24th, 2007, 10:13 AM
 Peso Friend of Wrox Join Date: May 2006 Posts: 246 Thanks: 0 Thanked 0 Times in 0 Posts

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

October 24th, 2007, 10:19 AM
 dparsons Wrox Author Join Date: Oct 2005 Posts: 4,104 Thanks: 1 Thanked 64 Times in 64 Posts

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

================================================== =========
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
================================================== =========

October 24th, 2007, 10:21 AM
 Jeff Moden Friend of Wrox Join Date: Oct 2006 Posts: 475 Thanks: 0 Thanked 9 Times in 9 Posts

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

--Jeff Moden

October 24th, 2007, 10:26 AM
 Jeff Moden Friend of Wrox Join Date: Oct 2006 Posts: 475 Thanks: 0 Thanked 9 Times in 9 Posts

Quote:
 quote:Originally posted by dparsons  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 ================================================== =========
% 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

October 24th, 2007, 10:26 AM
 dparsons Wrox Author Join Date: Oct 2005 Posts: 4,104 Thanks: 1 Thanked 64 Times in 64 Posts

What a duh moment. Modulus operator, right. wow I need a vacation =
================================================== =========
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
================================================== =========

 Similar Threads Thread Thread Starter Forum Replies Last Post Calculating time Marta Access 0 April 15th, 2008 10:34 PM calculating time differences vincee SQL Server 2000 6 January 24th, 2006 11:02 AM Calculating Time elapsed on form load Brendan Bartley Access 0 June 21st, 2005 09:02 AM Problems with" i=i+1" and calculating time tronnen XSLT 4 June 6th, 2005 04:17 PM Missing time part while calculating the Delay happygv SQL Language 2 October 29th, 2003 11:57 PM