datediff - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

datediff

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-16 : 10:41:13

when I do a datediff such as:
select 1.0 * datediff(hh, '2010-11-16 08:30:49.570', getdate())/2395
the answer is 0.3

Question is, what does 0.3 or say 0.78 represent?
I do not think that 0.3 represents 30 minutes or 0.78 represent 38 minutes?
Do you know?
Thanks

Kristen
Test

22859 Posts

Posted - 2010-12-16 : 11:09:33

It is 0.3 "2395ths" of a hour

Although what use "2395ths" of an hour are I'm not sure

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-16 : 11:14:23

what does that mean?
Can you please explain?
so, how do I work out what 1.78 refers to in minutes?
Thanks

Kristen
Test

22859 Posts

Posted - 2010-12-16 : 11:29:25

DATEDIFF(HH, ...

gives you the difference in hours.

You are then dividing that by 2,395.

So what you get is N "2395ths" of an hour.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-16 : 11:36:16

So how is 1.78 calculated in minutes?
is it 1.78/2395 ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-16 : 12:16:10

Actally N (or 0.3) is 2395ths of the differnce in hours. Not part of an hour. In the example above the differnce in hours is (although impossible) 718.5. so:

N   = H     / 2395
0.3 = 718.5 / 2395

@Arkiboy, if you have a question, can you rephrase it with the assumption that we know nothing about what you are doing? Because we don't.

Kristen
Test

22859 Posts

Posted - 2010-12-16 : 12:34:15

I don't think you can calculate 1.78 minutes - as initially you just get the number of whole hours difference (no decimal part)

Kristen
Test

22859 Posts

Posted - 2010-12-16 : 12:36:07

quote:
Originally posted by Lamprey

Not part of an hour.

Quite

An important, but often not understood, point.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-16 : 13:54:19

How can I get the datediff(hh... so that I get my answer in hours:minutes:seconds
Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-16 : 14:27:18

[code]Declare @dt1 datetime
Declare @dt2 datetime

set @dt1 = '2010-12-16 08:30:49.570'
set @dt2 = getdate()

SELECT DateDiff(second, @dt1, @dt2) / 3600 [hours],
(DateDiff(second, @dt1, @dt2) % 3600) / 60 [minutes],
(DateDiff(second, @dt1, @dt2) % 3600) % 60 [seconds]

SELECT Convert(varchar(4), DateDiff(second, @dt1, @dt2) / 3600) + ':' +
Convert(varchar(4), (DateDiff(second, @dt1, @dt2) % 3600) / 60) + ':' +
Convert(varchar(4), (DateDiff(second, @dt1, @dt2) % 3600) % 60)
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-16 : 16:05:39

Another Option:

Declare @dt1 datetime
Declare @dt2 datetime

set	@dt1 = '2010-12-16 08:30:49.570'
set	@dt2 = getdate()

SELECT RIGHT(CONVERT(VARCHAR(23), DATEADD(SECOND, DATEDIFF(second, @dt1, @dt2), 0), 121), 12)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-16 : 17:12:24

Nice

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-17 : 02:21:56

I see...
so I can do something like:
[hh:mm:ss] = RIGHT(CONVERT(VARCHAR(23), DATEADD(SECOND, MydatediffValue, 0), 121), 12)

Thank you all

Kristen
Test

22859 Posts

Posted - 2010-12-17 : 03:17:22

"so I can do something like:"

Well ... you'll get modulo results if the difference is more than 24 hours - but it is fine if less then 24 hours

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-17 : 05:12:12

In that case how can my query below be modified to give correct result in hh:mm:ss in any case i.e. if less or more than 24 hrs?
[hh:mm:ss] = RIGHT(CONVERT(VARCHAR(23), DATEADD(SECOND, MydatediffValue, 0), 121), 12)

Thank you

Kristen
Test

22859 Posts

Posted - 2010-12-17 : 07:33:40

Use Russell's query - you will probably want to pad minutes / seconds with leading zero

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-12-17 : 08:49:48

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources