minutes difference - 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 2008 Forums

Transact-SQL (2008)

minutes difference

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-26 : 07:04:44

the below is my query:

DECLARE @INTIME TIME(0)
DECLARE @OUTTIME TIME(0)

SET @INTIME='09:00:00'
SET @OUTTIME='19:30:00'

SELECT
DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0

the output is : 10.500000

but i want it as 10.5 only
i used trim function, but it doesnt work

pls give the solution.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-26 : 07:11:09

round() ?

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 07:13:01

[code]
SELECT
LEFT(DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0,5)
[/code]

By the way what did you do for the problem you posted in your previous post?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 07:59:01

quote:
Originally posted by Arun.G

the below is my query:

DECLARE @INTIME TIME(0)
DECLARE @OUTTIME TIME(0)

SET @INTIME='09:00:00'
SET @OUTTIME='19:30:00'

SELECT
DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0

the output is : 10.500000

but i want it as 10.5 only
i used trim function, but it doesnt work

pls give the solution.

How are you going to find difference between INTIME='10:01:00' and OUTTIME='11:59:00'?
By your method difference comes to 1.98 hrs.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-26 : 08:09:52

quote:
Originally posted by Arun.G

the below is my query:

DECLARE @INTIME TIME(0)
DECLARE @OUTTIME TIME(0)

SET @INTIME='09:00:00'
SET @OUTTIME='19:30:00'

SELECT
DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0

the output is : 10.500000

but i want it as 10.5 only
i used trim function, but it doesnt work

pls give the solution.

SELECT	convert(decimal(5,1), DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0)

KH
[spoiler]Time is always against us[/spoiler]

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-26 : 08:11:21

THANKS Ideara, it works fine

but i want 10.5 only
so i used
SELECT
LEFT(DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0,4)- it works fine

but
if
SET @INTIME='09:00:00'
SET @OUTTIME='17:30:00'

so expected out is : 8.5
but its displaying: 8.50
bcoz of LEFT(DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0,4), it takes 4 digits from left,
if
LEFT(DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0,3) then it will give 9.5

but i cant give
LEFT(DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0,4)
LEFT(DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0,3) both in my sp

how to handle these conditions? any method?

ANYWAY THANKS A LOT

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 08:15:55

Use Khtan's method


SELECT	convert(decimal(5,1), DATEDIFF(MINUTE,@INTIME,@OUTTIME)/60.0)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-26 : 08:17:02

thanks man

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources