SQL RIGHT function not working as expected
I'm trying to extract the month number from a date as a left padded string with 0's.
So, for example, from '2018-01-31' I want the string '01'.
Currently I have this:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS CHAR(2)), 2)
Which is returning '1' but I would have expected it to return '01' because I've provided the second argument to RIGHT as 2.
Could someone explain why this isn't working as I think it should?
tsql
edited Nov 23 '18 at 14:50
Birel
447213
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
add a comment |
I'm trying to extract the month number from a date as a left padded string with 0's.
So, for example, from '2018-01-31' I want the string '01'.
Currently I have this:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS CHAR(2)), 2)
Which is returning '1' but I would have expected it to return '01' because I've provided the second argument to RIGHT as 2.
Could someone explain why this isn't working as I think it should?
tsql
edited Nov 23 '18 at 14:50
Birel
447213
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
FYI SQL 2012+ - format() can do this if you specify 'MM'
– Alex K.
Nov 23 '18 at 12:46
add a comment |
I'm trying to extract the month number from a date as a left padded string with 0's.
So, for example, from '2018-01-31' I want the string '01'.
Currently I have this:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS CHAR(2)), 2)
Which is returning '1' but I would have expected it to return '01' because I've provided the second argument to RIGHT as 2.
Could someone explain why this isn't working as I think it should?
tsql
edited Nov 23 '18 at 14:50
Birel
447213
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
I'm trying to extract the month number from a date as a left padded string with 0's.
So, for example, from '2018-01-31' I want the string '01'.
Currently I have this:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS CHAR(2)), 2)
Which is returning '1' but I would have expected it to return '01' because I've provided the second argument to RIGHT as 2.
Could someone explain why this isn't working as I think it should?
tsql
tsql
edited Nov 23 '18 at 14:50
Birel
447213
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
edited Nov 23 '18 at 14:50
Birel
447213
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
edited Nov 23 '18 at 14:50
Birel
447213
edited Nov 23 '18 at 14:50
Birel
447213
edited Nov 23 '18 at 14:50
Birel
447213
447213
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
asked Nov 23 '18 at 12:41
the_docthe_doc
340413
340413
FYI SQL 2012+ - format() can do this if you specify 'MM'
– Alex K.
Nov 23 '18 at 12:46
add a comment |
FYI SQL 2012+ - format() can do this if you specify 'MM'
– Alex K.
Nov 23 '18 at 12:46
FYI SQL 2012+ - format() can do this if you specify 'MM'
– Alex K.
Nov 23 '18 at 12:46
FYI SQL 2012+ - format() can do this if you specify 'MM'
– Alex K.
Nov 23 '18 at 12:46
add a comment |
2 Answers
2
active
oldest
votes
You need to change CHAR to VARCHAR:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS VARCHAR(2)), 2)
db<>fiddle demo
CHAR(2) is blank padded so you get RIGHT('01 ',2) which is '1 '.
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
Thanks for the answer, are you able to explain why it works for VARCHAR and not for CHAR?
– the_doc
Nov 23 '18 at 12:44
@the_doc Sure, CHAR is space padded on the right.
– Lukasz Szozda
Nov 23 '18 at 12:44
add a comment |
You could use FORMAT instead, when you first cast the string to a DATE type.
SELECT FORMAT(CAST('2018-01-31' as DATE),'MM')
As for why that SQL with the right didn't work?
Try this SQL and notice the difference (the extra space):
SELECT quotename('0' + CAST(1 AS CHAR(2))), quotename('0' + CAST(1 AS VARCHAR(2)))
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
add a comment |
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
});
}
});
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%2f53446923%2fsql-right-function-not-working-as-expected%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to change CHAR to VARCHAR:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS VARCHAR(2)), 2)
db<>fiddle demo
CHAR(2) is blank padded so you get RIGHT('01 ',2) which is '1 '.
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
Thanks for the answer, are you able to explain why it works for VARCHAR and not for CHAR?
– the_doc
Nov 23 '18 at 12:44
@the_doc Sure, CHAR is space padded on the right.
– Lukasz Szozda
Nov 23 '18 at 12:44
add a comment |
You need to change CHAR to VARCHAR:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS VARCHAR(2)), 2)
db<>fiddle demo
CHAR(2) is blank padded so you get RIGHT('01 ',2) which is '1 '.
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
Thanks for the answer, are you able to explain why it works for VARCHAR and not for CHAR?
– the_doc
Nov 23 '18 at 12:44
@the_doc Sure, CHAR is space padded on the right.
– Lukasz Szozda
Nov 23 '18 at 12:44
add a comment |
You need to change CHAR to VARCHAR:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS VARCHAR(2)), 2)
db<>fiddle demo
CHAR(2) is blank padded so you get RIGHT('01 ',2) which is '1 '.
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
You need to change CHAR to VARCHAR:
SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS VARCHAR(2)), 2)
db<>fiddle demo
CHAR(2) is blank padded so you get RIGHT('01 ',2) which is '1 '.
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
edited Nov 23 '18 at 12:44
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
answered Nov 23 '18 at 12:43
Lukasz SzozdaLukasz Szozda
80.7k1068106
80.7k1068106
Thanks for the answer, are you able to explain why it works for VARCHAR and not for CHAR?
– the_doc
Nov 23 '18 at 12:44
@the_doc Sure, CHAR is space padded on the right.
– Lukasz Szozda
Nov 23 '18 at 12:44
add a comment |
Thanks for the answer, are you able to explain why it works for VARCHAR and not for CHAR?
– the_doc
Nov 23 '18 at 12:44
@the_doc Sure, CHAR is space padded on the right.
– Lukasz Szozda
Nov 23 '18 at 12:44
Thanks for the answer, are you able to explain why it works for VARCHAR and not for CHAR?
– the_doc
Nov 23 '18 at 12:44
Thanks for the answer, are you able to explain why it works for VARCHAR and not for CHAR?
– the_doc
Nov 23 '18 at 12:44
@the_doc Sure, CHAR is space padded on the right.
– Lukasz Szozda
Nov 23 '18 at 12:44
@the_doc Sure, CHAR is space padded on the right.
– Lukasz Szozda
Nov 23 '18 at 12:44
add a comment |
You could use FORMAT instead, when you first cast the string to a DATE type.
SELECT FORMAT(CAST('2018-01-31' as DATE),'MM')
As for why that SQL with the right didn't work?
Try this SQL and notice the difference (the extra space):
SELECT quotename('0' + CAST(1 AS CHAR(2))), quotename('0' + CAST(1 AS VARCHAR(2)))
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
add a comment |
You could use FORMAT instead, when you first cast the string to a DATE type.
SELECT FORMAT(CAST('2018-01-31' as DATE),'MM')
As for why that SQL with the right didn't work?
Try this SQL and notice the difference (the extra space):
SELECT quotename('0' + CAST(1 AS CHAR(2))), quotename('0' + CAST(1 AS VARCHAR(2)))
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
add a comment |
You could use FORMAT instead, when you first cast the string to a DATE type.
SELECT FORMAT(CAST('2018-01-31' as DATE),'MM')
As for why that SQL with the right didn't work?
Try this SQL and notice the difference (the extra space):
SELECT quotename('0' + CAST(1 AS CHAR(2))), quotename('0' + CAST(1 AS VARCHAR(2)))
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
You could use FORMAT instead, when you first cast the string to a DATE type.
SELECT FORMAT(CAST('2018-01-31' as DATE),'MM')
As for why that SQL with the right didn't work?
Try this SQL and notice the difference (the extra space):
SELECT quotename('0' + CAST(1 AS CHAR(2))), quotename('0' + CAST(1 AS VARCHAR(2)))
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
answered Nov 23 '18 at 12:55
LukStormsLukStorms
13k31733
13k31733
add a comment |
add a comment |
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.
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%2f53446923%2fsql-right-function-not-working-as-expected%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
FYI SQL 2012+ - format() can do this if you specify 'MM'
– Alex K.
Nov 23 '18 at 12:46