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)
 DateDiff help

Author  Topic 

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-08-14 : 11:51:19
If I use the below computed column formula I get minutes...

(CONVERT([varchar],datediff(minute,[starttime],[endtime]),(0)))

What I need is to divide by 60. How do I add on a / 60 to this? ...I can't seem to get the syntax correct.

-Mike

Mike Brown

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-08-14 : 11:54:46
UPDATE:

I figured it out:

(CONVERT([varchar],datediff(minute,[starttime],[endtime])/(60),0))

HOWEVER my result is now rounding. I don't want SQL to round up or down. I want 90 minutes to be 1.5, 45 minutes 0.75, etc., etc.

How can i stop the rounding?

-Mike

Mike Brown
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-14 : 12:18:17
This is because of integer division. Force floating point division by making at least one of the numerator and denominator into a non-integer, like so:
(CONVERT([varchar],datediff(minute,[starttime],[endtime])/(60E),0))


There are couple of other things that you might want to consider:

1. When you declare/specify a varchar column, always specify a length. For example, VARCHAR(32), rather than just VARCHAR. http://myshallowsqlblog.wordpress.com/size-does-matter/

2. Unless you have specific need to convert to character type, keep the data as a numeric type. That will be more handy for all kinds of things - data integrity, sorting and filtering etc.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-14 : 13:27:30
I'd avoid "60E", since that gets you into floating point, i.e. approximate-only, values. Use decimal to get a specific result.

Rather than CONVERT, CAST to the specific decimal result you'd like to get. For example:

CAST(datediff(minute,[starttime],[endtime]) / 60.0 AS decimal(6, 2))


[That will round, but to two decimal places.]
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-14 : 14:35:22
I tend to avoid decimal in favor of floating point unless there is a compelling reason to use decimals. One compelling reason, of course, is that you may want exact numerics and decimal is an "exact numeric".

But the arguments for using float is that unless you are careful, decimals can result in less accurate results. See the examples below:
DECLARE @x FLOAT = 2.0
DECLARE @y FLOAT = 3.0;
SELECT @x/@y; -- gives 0.666666666666667
GO

DECLARE @x DECIMAL(38,20) = 2.0
DECLARE @y DECIMAL(38,20) = 3.0;
SELECT @x/@y -- gives 0.666666666
GO

While it may not matter in this case, in numerical computations (solving even relatively small system of linear equations for example), the cumulative errors can be significant.


quote:
Rather than CONVERT, CAST to the specific decimal result you'd like to get. For example:
Is the a reason to use CAST over CONVERT? I usually use cast unless I want to specify a style because it is a little less typing. But I am not aware of any advantages to using cast over convert.
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-08-14 : 14:37:53
Thank you both for help and explanations!!

-Mike

Mike Brown
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-14 : 19:09:52
quote:
Originally posted by James K
quote:
Rather than CONVERT, CAST to the specific decimal result you'd like to get. For example:

Is the[re] a reason to use CAST over CONVERT? I usually use cast unless I want to specify a style because it is a little less typing. But I am not aware of any advantages to using cast over convert.



CAST will always round, I don't think CONVERT does.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-14 : 19:13:43
I can't imagine why you'd even need to be accurate to billionths of an hour (except perhaps in scientific tests, where'd you be using exponential values for everything).
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-14 : 21:07:21
quote:
Originally posted by ScottPletcher

quote:
Originally posted by James K
quote:
Rather than CONVERT, CAST to the specific decimal result you'd like to get. For example:

Is the[re] a reason to use CAST over CONVERT? I usually use cast unless I want to specify a style because it is a little less typing. But I am not aware of any advantages to using cast over convert.



CAST will always round, I don't think CONVERT does.

Do you have any references or documentation that describes this? I don't see any difference in behavior as shown in the examples below. Nor do I see any mention of it on MSDN's page on CAST and CONVERT here: http://technet.microsoft.com/en-us/library/ms187928.aspx

DECLARE @x VARCHAR(32) = '0.666666666666666666666666666';
SELECT CAST(@x AS FLOAT), CONVERT(FLOAT,@x)

SELECT CAST(@x AS FLOAT)-0.666666666666667E, CONVERT(FLOAT,@x)-0.666666666666667E;

DECLARE @y DECIMAL(38,20) = 0.666666666666667;
SELECT CAST(@x AS FLOAT)-@y, CONVERT(FLOAT,@x)-@y
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-14 : 21:10:11
quote:
Originally posted by ScottPletcher

I can't imagine why you'd even need to be accurate to billionths of an hour (except perhaps in scientific tests, where'd you be using exponential values for everything).

May be it matters, or may be it doesn't. I was only addressing your statement that you would "avoid "60E", since that gets you into floating point, i.e. approximate-only". I was trying to point out that using exact numerics is a not a panacea.

Round-off errors do matter in more places than you might imagine. Linear algebra certainly is one place where it matters. Other places where it matters: http://mathworld.wolfram.com/RoundoffError.html

Go to Top of Page

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 11:28:22
CONVERT([varchar],datediff(minute,[starttime],[endtime])/(1E * 60),(0))
Go to Top of Page

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 11:48:36
Hi,
This may work fine for you...


DECLARE @MINUTE INT
SELECT @MINUTE = (CONVERT([varchar],datediff(minute,[starttime],[endtime]),(0)))
SELECT LEFT((CAST(@MINUTE AS VARCHAR)+'.0') /60.0,4)

P.Kameswara rao
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-08-29 : 03:45:53
Declare @StartTime datetime,@EndTime datetime
select(CONVERT(varchar,datediff(minute,@starttime,@endtime)/60,(0)))

veeranjaneyulu
Go to Top of Page
   

- Advertisement -