May 24, 2010 at 2:32 pm
Hugo Kornelis (5/24/2010)
CirquedeSQLeil (5/24/2010)
I see both sides of it. You, Trey and Hugo all have valid points.Please, don't let that discourage you from submitting more questions.
+1 on all points made by Jason (especially the "don't let that discourage" part - this audience has a tendency to scare off those who contribute freely...)
Thanks Hugo.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2010 at 2:47 pm
bkubicek (5/24/2010)
You are welcome.Thanks for not complaining about the formatting.
If I do another Question of the day I will have to watch my formatting.
Ben
Definately do another QOD. Don't pay too much attention to my complaining. 😉 Honestly I really appreciate the effort of the people who come up with these questions.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 24, 2010 at 3:17 pm
I really just guessed after about 30 seconds of trying to follow the logic. Only off by 1.0000!
May 25, 2010 at 2:38 am
Kenneth J. Moore (5/24/2010)
I really just guessed after about 30 seconds of trying to follow the logic. Only off by 1.0000!
Yeah that was me too. I looked at the date parsing stuff, got bored and skipped it, guessed it would be an integer division thing and took a guess at zero.
-------------------------------
Oh no!
May 25, 2010 at 2:26 pm
The integer division thing in general for me is often one of those "so simple you missed it situations". I'd be interested in what is considered best practice in real life for doing this properly.
To use this example:
select @hourDiff = @min-2 / cast(60 as numeric(10,4))
and
select @hourDiff = cast(@min as numeric(10,4)) / 60
Both give me 1.5. Which one of these options is the best way to go, or is there a better way of handling this issue?
May 25, 2010 at 5:04 pm
marklegosz (5/25/2010)
The integer division thing in general for me is often one of those "so simple you missed it situations". I'd be interested in what is considered best practice in real life for doing this properly.To use this example:
select @hourDiff = @min-2 / cast(60 as numeric(10,4))and
select @hourDiff = cast(@min as numeric(10,4)) / 60Both give me 1.5. Which one of these options is the best way to go, or is there a better way of handling this issue?
There is no real difference between the two. As soon as one of the operands for the diivision is numeric, the other will be converted to numeric as well. You can use what pleases your eyes the most.
May 25, 2010 at 5:05 pm
Why wouldn't you just divide by 60.0?
As soon as you add the .0 is becomes numeric.
Ben
May 25, 2010 at 5:08 pm
bkubicek (5/25/2010)
Why wouldn't you just divide by 60.0?As soon as you add the .0 is becomes numeric.
Ben
If numeric(3,1) works, then 60.0 is as good as any of the other options. If you need the precision of a numeric(10,4), the explicit cast is the way to go.
May 26, 2010 at 12:15 am
Hi,
select command in the code is used for assginign variables not to print any output.
the given code won't return any value.
Thanks,
RajaSekhar Reddy .K
May 26, 2010 at 12:57 am
bkubicek (5/25/2010)
Hugo,It is the precision of the variable you are putting the value into that matter. @min-2 is a numeric(10,4), so you will get your four decimal places using plain old 60.0.
Ben
I assume you meant to write that @hourDiff is nummeric(10,4); @min-2 is an integer. If that is what you meant, then you are right that the end result will always be converted to numeric(10,4). But intermediate results use a different precision; the casting to the target data type of numeric(10, 4) is the final step.
Run the following code to see how the data types used for the operands of the division affect the length and precision of the intermediate result. In this case, since the division result is exactly 1.5, there will not be any net effect. But there are cases where the number of decimals used in the intermediate result may affect the end result. (Keep in mind that the division result will be truncated to the precision of the intermediate result, which will then be rounded to the precision of the variable).
The difference will never be more than 0.0001, which admittedly is not significant in most cases. But in those cases where that difference is important, the difference between "60.0" and "CAST(60 AS numeric(10,4))" does matter.
May 26, 2010 at 12:59 am
Rajasekhar Reddy (5/26/2010)
Hi,select command in the code is used for assginign variables not to print any output.
the given code won't return any value.
Thanks,
RajaSekhar Reddy .K
That is entirely correct. Of course, the question was "what result will @hourdiff hold?", not "what will be returned when you run this code", so I fail to see the significance of this observation.
May 26, 2010 at 12:16 pm
What Hugo said. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 26, 2010 at 12:26 pm
I got it wrong. I answered "purple". :doze:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 26, 2010 at 12:29 pm
The Dixie Flatline (5/26/2010)
I got it wrong. I answered "purple". :doze:
*sound of a mouthful of tea hitting an LCD screen at high speed*
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply