Mysql selecting unique values in date type varchar
I have a column where the dates are type varchar. For example:
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
10-11-2018
12-11-2018
when I consult with the following query
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '9-11-2018'.
I have the right result.
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
but if the query is:
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '10-11-2018'.
or
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '12-11-2018'.
The answer I get is empty.
I think it's only validating the days in the sql.
I need to get the right dates.
mysql
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
add a comment |
I have a column where the dates are type varchar. For example:
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
10-11-2018
12-11-2018
when I consult with the following query
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '9-11-2018'.
I have the right result.
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
but if the query is:
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '10-11-2018'.
or
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '12-11-2018'.
The answer I get is empty.
I think it's only validating the days in the sql.
I need to get the right dates.
mysql
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
add a comment |
I have a column where the dates are type varchar. For example:
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
10-11-2018
12-11-2018
when I consult with the following query
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '9-11-2018'.
I have the right result.
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
but if the query is:
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '10-11-2018'.
or
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '12-11-2018'.
The answer I get is empty.
I think it's only validating the days in the sql.
I need to get the right dates.
mysql
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
I have a column where the dates are type varchar. For example:
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
10-11-2018
12-11-2018
when I consult with the following query
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '9-11-2018'.
I have the right result.
15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
but if the query is:
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '10-11-2018'.
or
SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '12-11-2018'.
The answer I get is empty.
I think it's only validating the days in the sql.
I need to get the right dates.
mysql
mysql
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
asked Nov 12 '18 at 23:01
Ricardo PrietoRicardo Prieto
154
154
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I think the problem is the fact that the column is varchar, so it's comparing characters instead of a range of dates. I will recommend convert the column to date type and try again.
Alternative if you cannot change the type of the column you could cast it to date format like this:
SELECT DISTINCT `date` FROM `test` WHERE STR_TO_DATE(`date`,'%d-%m-%Y') BETWEEN '2018-10-15' AND '2018-11-10';
I tested with your data and it works. Of course this could put some extra effort on the database and will not use indexes.
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
The result is Warning: #1292 Incorrect datetime value: '15-10-2018' and Warning: #1292 Incorrect datetime value: '16-10-2018'
– Ricardo Prieto
Nov 13 '18 at 0:11
Ok, I see the problem. those date formats are not SQL valid. The valid format for dates is YYYY-MM-DD. You will need heavy adjust those format on the SQL to make it works. For sake of database performance, I will suggest fix the format before input them, make the column date type.
– Carlos H.
Nov 13 '18 at 1:02
fixed the answer to use a correct function to fix the date format. Also fixed the range dates to be correct SQL compatible.
– Carlos H.
Nov 13 '18 at 1:28
add a comment |
You need to set the datatype to date and update your dates to be using date for a more reliable result. Once done you should be using the database format for the dates in your WHERE clause.
Try
SELECT DISTINCT date FROMtestWHERE date BETWEEN '2018-10-15' and '2018-11-10'
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
when changing the data type all dates in the table are given values of 0000-00-00
– Ricardo Prieto
Nov 13 '18 at 0:12
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%2f53271350%2fmysql-selecting-unique-values-in-date-type-varchar%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
I think the problem is the fact that the column is varchar, so it's comparing characters instead of a range of dates. I will recommend convert the column to date type and try again.
Alternative if you cannot change the type of the column you could cast it to date format like this:
SELECT DISTINCT `date` FROM `test` WHERE STR_TO_DATE(`date`,'%d-%m-%Y') BETWEEN '2018-10-15' AND '2018-11-10';
I tested with your data and it works. Of course this could put some extra effort on the database and will not use indexes.
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
The result is Warning: #1292 Incorrect datetime value: '15-10-2018' and Warning: #1292 Incorrect datetime value: '16-10-2018'
– Ricardo Prieto
Nov 13 '18 at 0:11
Ok, I see the problem. those date formats are not SQL valid. The valid format for dates is YYYY-MM-DD. You will need heavy adjust those format on the SQL to make it works. For sake of database performance, I will suggest fix the format before input them, make the column date type.
– Carlos H.
Nov 13 '18 at 1:02
fixed the answer to use a correct function to fix the date format. Also fixed the range dates to be correct SQL compatible.
– Carlos H.
Nov 13 '18 at 1:28
add a comment |
I think the problem is the fact that the column is varchar, so it's comparing characters instead of a range of dates. I will recommend convert the column to date type and try again.
Alternative if you cannot change the type of the column you could cast it to date format like this:
SELECT DISTINCT `date` FROM `test` WHERE STR_TO_DATE(`date`,'%d-%m-%Y') BETWEEN '2018-10-15' AND '2018-11-10';
I tested with your data and it works. Of course this could put some extra effort on the database and will not use indexes.
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
The result is Warning: #1292 Incorrect datetime value: '15-10-2018' and Warning: #1292 Incorrect datetime value: '16-10-2018'
– Ricardo Prieto
Nov 13 '18 at 0:11
Ok, I see the problem. those date formats are not SQL valid. The valid format for dates is YYYY-MM-DD. You will need heavy adjust those format on the SQL to make it works. For sake of database performance, I will suggest fix the format before input them, make the column date type.
– Carlos H.
Nov 13 '18 at 1:02
fixed the answer to use a correct function to fix the date format. Also fixed the range dates to be correct SQL compatible.
– Carlos H.
Nov 13 '18 at 1:28
add a comment |
I think the problem is the fact that the column is varchar, so it's comparing characters instead of a range of dates. I will recommend convert the column to date type and try again.
Alternative if you cannot change the type of the column you could cast it to date format like this:
SELECT DISTINCT `date` FROM `test` WHERE STR_TO_DATE(`date`,'%d-%m-%Y') BETWEEN '2018-10-15' AND '2018-11-10';
I tested with your data and it works. Of course this could put some extra effort on the database and will not use indexes.
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
I think the problem is the fact that the column is varchar, so it's comparing characters instead of a range of dates. I will recommend convert the column to date type and try again.
Alternative if you cannot change the type of the column you could cast it to date format like this:
SELECT DISTINCT `date` FROM `test` WHERE STR_TO_DATE(`date`,'%d-%m-%Y') BETWEEN '2018-10-15' AND '2018-11-10';
I tested with your data and it works. Of course this could put some extra effort on the database and will not use indexes.
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
edited Nov 13 '18 at 1:31
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
answered Nov 12 '18 at 23:07
Carlos H.Carlos H.
1546
1546
The result is Warning: #1292 Incorrect datetime value: '15-10-2018' and Warning: #1292 Incorrect datetime value: '16-10-2018'
– Ricardo Prieto
Nov 13 '18 at 0:11
Ok, I see the problem. those date formats are not SQL valid. The valid format for dates is YYYY-MM-DD. You will need heavy adjust those format on the SQL to make it works. For sake of database performance, I will suggest fix the format before input them, make the column date type.
– Carlos H.
Nov 13 '18 at 1:02
fixed the answer to use a correct function to fix the date format. Also fixed the range dates to be correct SQL compatible.
– Carlos H.
Nov 13 '18 at 1:28
add a comment |
The result is Warning: #1292 Incorrect datetime value: '15-10-2018' and Warning: #1292 Incorrect datetime value: '16-10-2018'
– Ricardo Prieto
Nov 13 '18 at 0:11
Ok, I see the problem. those date formats are not SQL valid. The valid format for dates is YYYY-MM-DD. You will need heavy adjust those format on the SQL to make it works. For sake of database performance, I will suggest fix the format before input them, make the column date type.
– Carlos H.
Nov 13 '18 at 1:02
fixed the answer to use a correct function to fix the date format. Also fixed the range dates to be correct SQL compatible.
– Carlos H.
Nov 13 '18 at 1:28
The result is Warning: #1292 Incorrect datetime value: '15-10-2018' and Warning: #1292 Incorrect datetime value: '16-10-2018'
– Ricardo Prieto
Nov 13 '18 at 0:11
The result is Warning: #1292 Incorrect datetime value: '15-10-2018' and Warning: #1292 Incorrect datetime value: '16-10-2018'
– Ricardo Prieto
Nov 13 '18 at 0:11
Ok, I see the problem. those date formats are not SQL valid. The valid format for dates is YYYY-MM-DD. You will need heavy adjust those format on the SQL to make it works. For sake of database performance, I will suggest fix the format before input them, make the column date type.
– Carlos H.
Nov 13 '18 at 1:02
Ok, I see the problem. those date formats are not SQL valid. The valid format for dates is YYYY-MM-DD. You will need heavy adjust those format on the SQL to make it works. For sake of database performance, I will suggest fix the format before input them, make the column date type.
– Carlos H.
Nov 13 '18 at 1:02
fixed the answer to use a correct function to fix the date format. Also fixed the range dates to be correct SQL compatible.
– Carlos H.
Nov 13 '18 at 1:28
fixed the answer to use a correct function to fix the date format. Also fixed the range dates to be correct SQL compatible.
– Carlos H.
Nov 13 '18 at 1:28
add a comment |
You need to set the datatype to date and update your dates to be using date for a more reliable result. Once done you should be using the database format for the dates in your WHERE clause.
Try
SELECT DISTINCT date FROMtestWHERE date BETWEEN '2018-10-15' and '2018-11-10'
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
when changing the data type all dates in the table are given values of 0000-00-00
– Ricardo Prieto
Nov 13 '18 at 0:12
add a comment |
You need to set the datatype to date and update your dates to be using date for a more reliable result. Once done you should be using the database format for the dates in your WHERE clause.
Try
SELECT DISTINCT date FROMtestWHERE date BETWEEN '2018-10-15' and '2018-11-10'
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
when changing the data type all dates in the table are given values of 0000-00-00
– Ricardo Prieto
Nov 13 '18 at 0:12
add a comment |
You need to set the datatype to date and update your dates to be using date for a more reliable result. Once done you should be using the database format for the dates in your WHERE clause.
Try
SELECT DISTINCT date FROMtestWHERE date BETWEEN '2018-10-15' and '2018-11-10'
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
You need to set the datatype to date and update your dates to be using date for a more reliable result. Once done you should be using the database format for the dates in your WHERE clause.
Try
SELECT DISTINCT date FROMtestWHERE date BETWEEN '2018-10-15' and '2018-11-10'
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
answered Nov 12 '18 at 23:08
Simon RSimon R
2,27332332
2,27332332
when changing the data type all dates in the table are given values of 0000-00-00
– Ricardo Prieto
Nov 13 '18 at 0:12
add a comment |
when changing the data type all dates in the table are given values of 0000-00-00
– Ricardo Prieto
Nov 13 '18 at 0:12
when changing the data type all dates in the table are given values of 0000-00-00
– Ricardo Prieto
Nov 13 '18 at 0:12
when changing the data type all dates in the table are given values of 0000-00-00
– Ricardo Prieto
Nov 13 '18 at 0:12
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%2f53271350%2fmysql-selecting-unique-values-in-date-type-varchar%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
