Date Time Difference in SQL Server 2000

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

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.

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources