SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Date Time Difference in SQL Server 2000
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/06/2005 :  07:56:34  Show Profile  Visit AskSQLTeam's Homepage
Jafer writes "I have 2 fields declared as DATETIME and they store both Date and Time value. Ex: A=2005-08-06 12:08:00.000,
B = 2005-08-18 00:30:19.000
I want to find the B-A, considering the time part. When I use the datediff function it considers only the date. I'm looking for a value something like 11.52 days. Is there a direct way to do this or how this can be calculated?"

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 10/06/2005 :  07:59:55  Show Profile  Send madhivanan a Yahoo! Message
Select DateDiff(day, date1, date2)
Select DateDiff(minute, date1, date2)

Combine both results

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 10/06/2005 :  08:02:29  Show Profile  Visit ditch's Homepage
There are 60 seconds in a Minute, 60 Minutes in a hour, 24 hours in a day - use these values in your query
DECLARE @A DATETIME
DECLARE @B DATETIME
DECLARE @Secs DECIMAL(18, 2)

SET @A = '2005-08-06 12:08:00.000'
SET @B = '2005-08-18 00:30:19.000'

SELECT @Secs = DATEDIFF(s, @A, @B) / 60.000000 / 60.00000 / 24.000000
select @Secs

Duane.
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 10/06/2005 :  08:05:07  Show Profile  Visit ditch's Homepage
quote:
Originally posted by madhivanan

Select DateDiff(day, date1, date2)
Select DateDiff(minute, date1, date2)

Combine both results




I dont think so that gives 11 days 16582 minutes

Duane.
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/06/2005 :  08:13:38  Show Profile
Just subtract one from the other and cast to numeric (or decimal or float, etc.)


SELECT CAST(b - a AS numeric(18,2))
FROM (
    SELECT
      CAST('2005-08-06 12:08:00.000' AS datetime) AS a,
      CAST('2005-08-18 00:30:19.000' AS datetime) AS b
  ) AS A


11.52

Edited by - Arnold Fribble on 10/06/2005 08:16:28
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000