SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


cast, convert and float!


cast, convert and float!

Author
Message
Toreador
Toreador
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3871 Visits: 8128
A pity that 11 wasn't one of the options - then we'd have had to decide whether casting 10.5 as an integer truncated or rounded (and I would probably have guessed wrong!)
Jim Norcal
Jim Norcal
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 24
I think the 30% of us (me included) that got it wrong just need to make sure we get our morning caffiene before trying to answer the questions [Wink]


Yea! That's right! I'm only five short sips into my coke this morning so my wrong answer way way didn't count! :-D

I'm guessing that the first @c declaration made it an implied int, which I didn't get right off. I thought that it would change once it was converted to a floating point integer. I've been out of this game for a while now. It's amazing on how much you start to lose ... or middle age is finally starting to play a small role.
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 169
Re SET and SELECT, is there a realistic scenario OTHER than capturing previous statement's @@ERROR and @@ROWCOUNT where a single statement must be used? I think it's the only one that I've wanted.

I hadn't thought of the trick on that Web page of converting them to strings and concatenating them, to get both values captured by a SET statement. Effective - but ugly.

I suppose there isn't much point asking whether ANSI requires that each SET statement resets @@ROWCOUNT to 1 after execution - whether that's an ANSI rule or not, we are stuck with it. (Or are we?)
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4105 Visits: 2935
Jim in Arizona (8/20/2009)
I think the 30% of us (me included) that got it wrong just need to make sure we get our morning caffiene before trying to answer the questions [Wink]


Yea! That's right! I'm only five short sips into my coke this morning so my wrong answer way way didn't count! :-D

I'm guessing that the first @c declaration made it an implied int, which I didn't get right off. I thought that it would change once it was converted to a floating point integer. I've been out of this game for a while now. It's amazing on how much you start to lose ... or middle age is finally starting to play a small role.


A floating point integer? I think you might need a stronger drink, Jim... :-P
Jim Norcal
Jim Norcal
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 24
A floating point integer? I think you might need a stronger drink, Jim... [Tongue]


Yea, it's not my day, let me tell ya. It sounded good though. :-)
macrostarrphish
macrostarrphish
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 155
That is good to know the next time I want to:

A.) Convert an interger to a varchar then
B.) Convert that varchar to a smaller varchar then
C.) Cast said varchar as a float then
D.) Store float as an integer then
E.) Select the output for use somewhere else...



DECLARE @c INT,@d INT
SET @c = 211
SET @d = CAST(CONVERT(VARCHAR(2),CONVERT(VARCHAR(3),@c)) as FLOAT) * 0.5
SELECT @d
Jim Norcal
Jim Norcal
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 24

That is good to know the next time I want to:

A.) Convert an interger to a varchar then
B.) Convert that varchar to a smaller varchar then
C.) Cast said varchar as a float then
D.) Store float as an integer then
E.) Select the output for use somewhere else...


Yea, you're right. This is good practice for the next time I have to do such tasks.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19141 Visits: 12426
JacekO (8/20/2009)
Scary part is over 30% (at the time of this post) got it wrong. Many QoDs are quite tricky but this one is very basic and really not SQL specific (data type conversion or casting is an ABC of any programming language).
Looks like a concept of code review or inspection is missing way too often...


I'm part of that 30%. (32% by now...)

In my case, the problem was that I spent all my energy at dissecting and understanding the nested CONVERT and CAST operations, and when I was finished I immediately answered without noticing that the result was first assigned to a variable before displaying it. Oh well.

On a side note, the explanation is not entirely correct. It says: "which, multiplied by 0.5 gives 10 (and not 10.5) as @c is of int datatype". Better would have been to write "which, multiplied by 0.5 gives 10.5, which is then truncated to 10 as @c is of int datatype". But maybe that's a bit too nitpicky?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
kramaswamy
kramaswamy
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 1832
same as everyone else who got it wrong ... focussed on the varchar and float operations, and totally forgot it was being assigned to an int at the end :/
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 169
Jim in Arizona (8/20/2009)
A floating point integer? I think you might need a stronger drink, Jim... [Tongue]


Yea, it's not my day, let me tell ya. It sounded good though. :-)

Everyone's "voice" here sounds equally authoritative - we should have different fonts for different levels of perceived authority. Dibs on ancient Gothic :-) ("Cuneiform", what's that??)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search