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.-MikeMike 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?-MikeMike Brown |
|
|
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. |
|
|
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.] |
|
|
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.0DECLARE @y FLOAT = 3.0;SELECT @x/@y; -- gives 0.666666666666667GODECLARE @x DECIMAL(38,20) = 2.0DECLARE @y DECIMAL(38,20) = 3.0;SELECT @x/@y -- gives 0.666666666GO 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. |
|
|
mikeallenbrown
Yak Posting Veteran
72 Posts |
Posted - 2013-08-14 : 14:37:53
|
Thank you both for help and explanations!!-MikeMike Brown |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-08-14 : 19:09:52
|
quote: Originally posted by James Kquote: 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. |
|
|
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). |
|
|
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 Kquote: 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.aspxDECLARE @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 |
|
|
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 |
|
|
ShivaKrishna
Starting Member
20 Posts |
Posted - 2013-08-28 : 11:28:22
|
CONVERT([varchar],datediff(minute,[starttime],[endtime])/(1E * 60),(0)) |
|
|
kameswararao polireddy
Starting Member
19 Posts |
Posted - 2013-08-28 : 11:48:36
|
Hi,This may work fine for you...DECLARE @MINUTE INTSELECT @MINUTE = (CONVERT([varchar],datediff(minute,[starttime],[endtime]),(0)))SELECT LEFT((CAST(@MINUTE AS VARCHAR)+'.0') /60.0,4)P.Kameswara rao |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-08-29 : 03:45:53
|
Declare @StartTime datetime,@EndTime datetimeselect(CONVERT(varchar,datediff(minute,@starttime,@endtime)/60,(0)))veeranjaneyulu |
|
|
|