query doubt - 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

General SQL Server Forums

New to SQL Server Programming

query doubt

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-28 : 07:33:48

hi folks,

i want the difference of 15 mints i.e 24.15000 but it is giving 24.45000

select (datediff(mi,'2007-04-28 14:30:14.347','2007-04-29 14:45:14.347')/60.0)

Vinod
Even you learn 1%, Learn it with 100% confidence.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 07:39:11

why are you doing .../60.0?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-28 : 07:42:52

to convert into minutes

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 07:43:15

How come you are getting, 24.45? I am getting 24.25 which I guess is correct.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-28 : 07:44:49

Date diff is comming as 24.250000
but i want the output as 24.150000

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-28 : 07:48:24

[code]
select diff_min / 60 as hours, diff_min % 60 as mins
from
(
select diff_min = datediff(minute, '2007-04-28 14:30:14.347', '2007-04-29 14:45:14.347')
) a
[/code]

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-28 : 07:55:32

Greatly working this query KH, but i didn't get the logic..... can you please spare 2 mts for me to explain....

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-28 : 08:03:26

the inner query will calculate the difference of 2 time in minute.

To convert to hour and minute just the the diff in minutes divide by 60 to get the quotient and remainder.
diff_min / 60 will give you the quotient
diff_min % 60 will give you the remainder

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-28 : 08:05:48

Great
thank you KH

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources