Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
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
 
Old October 17th, 2007, 03:35 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default 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
========================
 
Old October 17th, 2007, 03:56 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
 
Old October 17th, 2007, 04:20 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old October 17th, 2007, 06:53 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
========================
 
Old October 17th, 2007, 07:58 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old October 24th, 2007, 10:13 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old October 24th, 2007, 10:19 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old October 24th, 2007, 10:21 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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

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

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
 
Old October 24th, 2007, 10:26 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.