SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 DateDiff help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 08/14/2013 :  11:51:19  Show Profile  Reply with Quote
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

USA
53 Posts

Posted - 08/14/2013 :  11:54:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/14/2013 :  12:18:17  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
381 Posts

Posted - 08/14/2013 :  13:27:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/14/2013 :  14:35:22  Show Profile  Reply with Quote
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

USA
53 Posts

Posted - 08/14/2013 :  14:37:53  Show Profile  Reply with Quote
Thank you both for help and explanations!!

-Mike

Mike Brown
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
381 Posts

Posted - 08/14/2013 :  19:09:52  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
381 Posts

Posted - 08/14/2013 :  19:13:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/14/2013 :  21:07:21  Show Profile  Reply with Quote
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

Edited by - James K on 08/14/2013 21:15:18
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/14/2013 :  21:10:11  Show Profile  Reply with Quote
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


Edited by - James K on 08/14/2013 21:11:14
Go to Top of Page

ShivaKrishna
Starting Member

India
20 Posts

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

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/28/2013 :  11:48:36  Show Profile  Reply with Quote
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

India
169 Posts

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

veeranjaneyulu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000