casting float vs numeric in sql server 2008
1
I have two queries casting the same number of 2 different data types into numeric data type
SELECT cast(cast(79.523500 as float) as numeric(20,3))
result:- 79.523
SELECT cast(cast(79.523500 as numeric(20,6)) as numeric(20,3))
result:- 79.524
Why is it 2 different answers for the same number?
sql
sql-server casting floating-point numeric edited Nov 13 '18 at 10:52
juergen d
160k24202260
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
add a comment |
1
I have two queries casting the same number of 2 different data types into numeric data type
SELECT cast(cast(79.523500 as float) as numeric(20,3))
result:- 79.523
SELECT cast(cast(79.523500 as numeric(20,6)) as numeric(20,3))
result:- 79.524
Why is it 2 different answers for the same number?
sql
sql-server casting floating-point numeric edited Nov 13 '18 at 10:52
juergen d
160k24202260
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
2
Becausefloatis not accurate, only approximate
– juergen d
Nov 13 '18 at 10:51
TheCASTrounds to the smallest precision for both. But rounding a float sometimes rounds half down. For exampleSELECT round(0.5550E0, 2), round(1.5550E0, 2)returns0.56 1.55. But you can trick it to always round half up by adding a tiny number to it. F.e.SELECT round(1.5550E0 + 1E-9, 2)returns1.56
– LukStorms
Nov 15 '18 at 13:00
add a comment |
1
1
1
0
I have two queries casting the same number of 2 different data types into numeric data type
SELECT cast(cast(79.523500 as float) as numeric(20,3))
result:- 79.523
SELECT cast(cast(79.523500 as numeric(20,6)) as numeric(20,3))
result:- 79.524
Why is it 2 different answers for the same number?
sql
sql-server casting floating-point numeric edited Nov 13 '18 at 10:52
juergen d
160k24202260
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
I have two queries casting the same number of 2 different data types into numeric data type
SELECT cast(cast(79.523500 as float) as numeric(20,3))
result:- 79.523
SELECT cast(cast(79.523500 as numeric(20,6)) as numeric(20,3))
result:- 79.524
Why is it 2 different answers for the same number?
sql
sql-server casting floating-point numeric sql
sql-server casting floating-point numeric edited Nov 13 '18 at 10:52
juergen d
160k24202260
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
edited Nov 13 '18 at 10:52
juergen d
160k24202260
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
edited Nov 13 '18 at 10:52
juergen d
160k24202260
edited Nov 13 '18 at 10:52
juergen d
160k24202260
edited Nov 13 '18 at 10:52
juergen d
160k24202260
160k24202260
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
asked Nov 13 '18 at 10:49
AADITHYA KRISHNANAADITHYA KRISHNAN
132
132
2
Becausefloatis not accurate, only approximate
– juergen d
Nov 13 '18 at 10:51
TheCASTrounds to the smallest precision for both. But rounding a float sometimes rounds half down. For exampleSELECT round(0.5550E0, 2), round(1.5550E0, 2)returns0.56 1.55. But you can trick it to always round half up by adding a tiny number to it. F.e.SELECT round(1.5550E0 + 1E-9, 2)returns1.56
– LukStorms
Nov 15 '18 at 13:00
add a comment |
2
Becausefloatis not accurate, only approximate
– juergen d
Nov 13 '18 at 10:51
TheCASTrounds to the smallest precision for both. But rounding a float sometimes rounds half down. For exampleSELECT round(0.5550E0, 2), round(1.5550E0, 2)returns0.56 1.55. But you can trick it to always round half up by adding a tiny number to it. F.e.SELECT round(1.5550E0 + 1E-9, 2)returns1.56
– LukStorms
Nov 15 '18 at 13:00
2
2
Because
float is not accurate, only approximate– juergen d
Nov 13 '18 at 10:51
Because
float is not accurate, only approximate– juergen d
Nov 13 '18 at 10:51
The
CAST rounds to the smallest precision for both. But rounding a float sometimes rounds half down. For example SELECT round(0.5550E0, 2), round(1.5550E0, 2) returns 0.56 1.55. But you can trick it to always round half up by adding a tiny number to it. F.e. SELECT round(1.5550E0 + 1E-9, 2) returns 1.56– LukStorms
Nov 15 '18 at 13:00
The
CAST rounds to the smallest precision for both. But rounding a float sometimes rounds half down. For example SELECT round(0.5550E0, 2), round(1.5550E0, 2) returns 0.56 1.55. But you can trick it to always round half up by adding a tiny number to it. F.e. SELECT round(1.5550E0 + 1E-9, 2) returns 1.56– LukStorms
Nov 15 '18 at 13:00
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279344%2fcasting-float-vs-numeric-in-sql-server-2008%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279344%2fcasting-float-vs-numeric-in-sql-server-2008%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Because
floatis not accurate, only approximate– juergen d
Nov 13 '18 at 10:51
The
CASTrounds to the smallest precision for both. But rounding a float sometimes rounds half down. For exampleSELECT round(0.5550E0, 2), round(1.5550E0, 2)returns0.56 1.55. But you can trick it to always round half up by adding a tiny number to it. F.e.SELECT round(1.5550E0 + 1E-9, 2)returns1.56– LukStorms
Nov 15 '18 at 13:00