STR_TO_DATE and SUBSTRING in it
Hi I would like to combine 3 substrings (year,month and day) from one string and put whole concatenation as date format,but I don't know what I am doing wrong. All characters of string are numbers (11).
SELECT str_to_date('SELECT CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2))
FROM table', '%y %m %d');
mysql
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
asked Nov 18 '18 at 4:34
MarkMark
83
add a comment |
Hi I would like to combine 3 substrings (year,month and day) from one string and put whole concatenation as date format,but I don't know what I am doing wrong. All characters of string are numbers (11).
SELECT str_to_date('SELECT CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2))
FROM table', '%y %m %d');
mysql
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
asked Nov 18 '18 at 4:34
MarkMark
83
add a comment |
Hi I would like to combine 3 substrings (year,month and day) from one string and put whole concatenation as date format,but I don't know what I am doing wrong. All characters of string are numbers (11).
SELECT str_to_date('SELECT CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2))
FROM table', '%y %m %d');
mysql
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
asked Nov 18 '18 at 4:34
MarkMark
83
Hi I would like to combine 3 substrings (year,month and day) from one string and put whole concatenation as date format,but I don't know what I am doing wrong. All characters of string are numbers (11).
SELECT str_to_date('SELECT CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2))
FROM table', '%y %m %d');
mysql
mysql
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
asked Nov 18 '18 at 4:34
MarkMark
83
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
asked Nov 18 '18 at 4:34
MarkMark
83
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
edited Nov 18 '18 at 4:35
Madhur Bhaiya
19.6k62236
19.6k62236
asked Nov 18 '18 at 4:34
MarkMark
83
asked Nov 18 '18 at 4:34
MarkMark
83
asked Nov 18 '18 at 4:34
MarkMark
83
83
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
We can do this with a single substring call:
SELECT
STR_TO_DATE(LEFT(String, 6), '%y%m%d')
FROM yourTable;
Note: You should avoid storing date information as plain text. My usual advice here would be to keep the data returned from STR_TO_DATE as a separate column, and then remove the text date column.
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
add a comment |
Assuming that String is a column name in your table. You do not need to specify SELECT .. FROM table inside the Concat() function. You can directly use the column values inside the function. Your approach was syntactically wrong as well. FROM table should be after the SELECT .. clause.
Now, in the specified date format '%y %m %d', there are spaces between yy, mm and dd. So either you will need to use spaces in-between during the concatenation:
SELECT str_to_date(CONCAT(substring(String,1,2), ' ',
substring(String,3,2), ' ',
substring(String,5,2)
)
, '%y %m %d')
FROM table
Or, you can get rid of the spaces from the format specified.
SELECT str_to_date(CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2)
)
, '%y%m%d')
FROM table
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
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%2f53357928%2fstr-to-date-and-substring-in-it%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
We can do this with a single substring call:
SELECT
STR_TO_DATE(LEFT(String, 6), '%y%m%d')
FROM yourTable;
Note: You should avoid storing date information as plain text. My usual advice here would be to keep the data returned from STR_TO_DATE as a separate column, and then remove the text date column.
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
add a comment |
We can do this with a single substring call:
SELECT
STR_TO_DATE(LEFT(String, 6), '%y%m%d')
FROM yourTable;
Note: You should avoid storing date information as plain text. My usual advice here would be to keep the data returned from STR_TO_DATE as a separate column, and then remove the text date column.
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
add a comment |
We can do this with a single substring call:
SELECT
STR_TO_DATE(LEFT(String, 6), '%y%m%d')
FROM yourTable;
Note: You should avoid storing date information as plain text. My usual advice here would be to keep the data returned from STR_TO_DATE as a separate column, and then remove the text date column.
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
We can do this with a single substring call:
SELECT
STR_TO_DATE(LEFT(String, 6), '%y%m%d')
FROM yourTable;
Note: You should avoid storing date information as plain text. My usual advice here would be to keep the data returned from STR_TO_DATE as a separate column, and then remove the text date column.
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
answered Nov 18 '18 at 4:39
Tim BiegeleisenTim Biegeleisen
224k1391143
224k1391143
add a comment |
add a comment |
Assuming that String is a column name in your table. You do not need to specify SELECT .. FROM table inside the Concat() function. You can directly use the column values inside the function. Your approach was syntactically wrong as well. FROM table should be after the SELECT .. clause.
Now, in the specified date format '%y %m %d', there are spaces between yy, mm and dd. So either you will need to use spaces in-between during the concatenation:
SELECT str_to_date(CONCAT(substring(String,1,2), ' ',
substring(String,3,2), ' ',
substring(String,5,2)
)
, '%y %m %d')
FROM table
Or, you can get rid of the spaces from the format specified.
SELECT str_to_date(CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2)
)
, '%y%m%d')
FROM table
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
add a comment |
Assuming that String is a column name in your table. You do not need to specify SELECT .. FROM table inside the Concat() function. You can directly use the column values inside the function. Your approach was syntactically wrong as well. FROM table should be after the SELECT .. clause.
Now, in the specified date format '%y %m %d', there are spaces between yy, mm and dd. So either you will need to use spaces in-between during the concatenation:
SELECT str_to_date(CONCAT(substring(String,1,2), ' ',
substring(String,3,2), ' ',
substring(String,5,2)
)
, '%y %m %d')
FROM table
Or, you can get rid of the spaces from the format specified.
SELECT str_to_date(CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2)
)
, '%y%m%d')
FROM table
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
add a comment |
Assuming that String is a column name in your table. You do not need to specify SELECT .. FROM table inside the Concat() function. You can directly use the column values inside the function. Your approach was syntactically wrong as well. FROM table should be after the SELECT .. clause.
Now, in the specified date format '%y %m %d', there are spaces between yy, mm and dd. So either you will need to use spaces in-between during the concatenation:
SELECT str_to_date(CONCAT(substring(String,1,2), ' ',
substring(String,3,2), ' ',
substring(String,5,2)
)
, '%y %m %d')
FROM table
Or, you can get rid of the spaces from the format specified.
SELECT str_to_date(CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2)
)
, '%y%m%d')
FROM table
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Assuming that String is a column name in your table. You do not need to specify SELECT .. FROM table inside the Concat() function. You can directly use the column values inside the function. Your approach was syntactically wrong as well. FROM table should be after the SELECT .. clause.
Now, in the specified date format '%y %m %d', there are spaces between yy, mm and dd. So either you will need to use spaces in-between during the concatenation:
SELECT str_to_date(CONCAT(substring(String,1,2), ' ',
substring(String,3,2), ' ',
substring(String,5,2)
)
, '%y %m %d')
FROM table
Or, you can get rid of the spaces from the format specified.
SELECT str_to_date(CONCAT(substring(String,1,2),
substring(String,3,2),
substring(String,5,2)
)
, '%y%m%d')
FROM table
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 18 '18 at 4:36
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
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%2f53357928%2fstr-to-date-and-substring-in-it%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
