Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Date Time Difference in SQL Server 2000

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-06 : 07:56:34
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

22864 Posts

Posted - 2005-10-06 : 07:59:55
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
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-06 : 08:02:29
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
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-06 : 08:05:07
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

1961 Posts

Posted - 2005-10-06 : 08:13:38
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
Go to Top of Page
   

- Advertisement -