Filter query by Dates Mysql
I am new to MySQL, and I am having a difficulties filtering based on the date in my WHERE clause.
I am comparing the date1 to 2018-01-01 and 2018-31-12. All of the dates within this range should be displayed, but when I run my query nothing is displayed.
I would really appreciate any advice.
The date1 datatype is longtext, and the val datatype is longtext.
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
2019-01-02 V4
2019-10-03 V5
This is my SQL query:
SELECT * FROM tbl1 WHERE date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31'
Expected Output:
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
mysql
edited Nov 14 '18 at 21:53
fubar
9,96121531
asked Nov 14 '18 at 21:43
a ca c
32
add a comment |
I am new to MySQL, and I am having a difficulties filtering based on the date in my WHERE clause.
I am comparing the date1 to 2018-01-01 and 2018-31-12. All of the dates within this range should be displayed, but when I run my query nothing is displayed.
I would really appreciate any advice.
The date1 datatype is longtext, and the val datatype is longtext.
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
2019-01-02 V4
2019-10-03 V5
This is my SQL query:
SELECT * FROM tbl1 WHERE date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31'
Expected Output:
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
mysql
edited Nov 14 '18 at 21:53
fubar
9,96121531
asked Nov 14 '18 at 21:43
a ca c
32
Hi really appeciate your response its a longtext
– a c
Nov 14 '18 at 21:51
It is best practice to store date values asdateordatetimedatatypes.
– fubar
Nov 14 '18 at 21:53
I am sorry thank you for the advice really appreciate
– a c
Nov 14 '18 at 21:58
add a comment |
I am new to MySQL, and I am having a difficulties filtering based on the date in my WHERE clause.
I am comparing the date1 to 2018-01-01 and 2018-31-12. All of the dates within this range should be displayed, but when I run my query nothing is displayed.
I would really appreciate any advice.
The date1 datatype is longtext, and the val datatype is longtext.
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
2019-01-02 V4
2019-10-03 V5
This is my SQL query:
SELECT * FROM tbl1 WHERE date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31'
Expected Output:
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
mysql
edited Nov 14 '18 at 21:53
fubar
9,96121531
asked Nov 14 '18 at 21:43
a ca c
32
I am new to MySQL, and I am having a difficulties filtering based on the date in my WHERE clause.
I am comparing the date1 to 2018-01-01 and 2018-31-12. All of the dates within this range should be displayed, but when I run my query nothing is displayed.
I would really appreciate any advice.
The date1 datatype is longtext, and the val datatype is longtext.
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
2019-01-02 V4
2019-10-03 V5
This is my SQL query:
SELECT * FROM tbl1 WHERE date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31'
Expected Output:
date1 Val
2018-01-03 V1
2018-07-05 V2
2018-12-05 V3
mysql
mysql
edited Nov 14 '18 at 21:53
fubar
9,96121531
asked Nov 14 '18 at 21:43
a ca c
32
edited Nov 14 '18 at 21:53
fubar
9,96121531
asked Nov 14 '18 at 21:43
a ca c
32
edited Nov 14 '18 at 21:53
fubar
9,96121531
edited Nov 14 '18 at 21:53
fubar
9,96121531
edited Nov 14 '18 at 21:53
fubar
9,96121531
9,96121531
asked Nov 14 '18 at 21:43
a ca c
32
asked Nov 14 '18 at 21:43
a ca c
32
asked Nov 14 '18 at 21:43
a ca c
32
32
Hi really appeciate your response its a longtext
– a c
Nov 14 '18 at 21:51
It is best practice to store date values asdateordatetimedatatypes.
– fubar
Nov 14 '18 at 21:53
I am sorry thank you for the advice really appreciate
– a c
Nov 14 '18 at 21:58
add a comment |
Hi really appeciate your response its a longtext
– a c
Nov 14 '18 at 21:51
It is best practice to store date values asdateordatetimedatatypes.
– fubar
Nov 14 '18 at 21:53
I am sorry thank you for the advice really appreciate
– a c
Nov 14 '18 at 21:58
Hi really appeciate your response its a longtext
– a c
Nov 14 '18 at 21:51
Hi really appeciate your response its a longtext
– a c
Nov 14 '18 at 21:51
It is best practice to store date values as
date or datetime datatypes.– fubar
Nov 14 '18 at 21:53
It is best practice to store date values as
date or datetime datatypes.– fubar
Nov 14 '18 at 21:53
I am sorry thank you for the advice really appreciate
– a c
Nov 14 '18 at 21:58
I am sorry thank you for the advice really appreciate
– a c
Nov 14 '18 at 21:58
add a comment |
1 Answer
1
active
oldest
votes
You have a syntax error.
mysql> Select * from foo where date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31' ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2018-01-01' AND date1<= '2018-12-31'' at line 1
YEAR '2018-01-01' is not valid syntax. It would have to be YEAR('2018-01-01').
BTW your query is better written with between. Same thing, easier to read, less chance of bugs.
select * from foo where date1 between '2018-01-01' AND '2018-12-31';
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
1
thank you it worked really appreciate your help
– a c
Nov 14 '18 at 21:59
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%2f53309167%2ffilter-query-by-dates-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You have a syntax error.
mysql> Select * from foo where date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31' ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2018-01-01' AND date1<= '2018-12-31'' at line 1
YEAR '2018-01-01' is not valid syntax. It would have to be YEAR('2018-01-01').
BTW your query is better written with between. Same thing, easier to read, less chance of bugs.
select * from foo where date1 between '2018-01-01' AND '2018-12-31';
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
1
thank you it worked really appreciate your help
– a c
Nov 14 '18 at 21:59
add a comment |
You have a syntax error.
mysql> Select * from foo where date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31' ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2018-01-01' AND date1<= '2018-12-31'' at line 1
YEAR '2018-01-01' is not valid syntax. It would have to be YEAR('2018-01-01').
BTW your query is better written with between. Same thing, easier to read, less chance of bugs.
select * from foo where date1 between '2018-01-01' AND '2018-12-31';
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
1
thank you it worked really appreciate your help
– a c
Nov 14 '18 at 21:59
add a comment |
You have a syntax error.
mysql> Select * from foo where date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31' ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2018-01-01' AND date1<= '2018-12-31'' at line 1
YEAR '2018-01-01' is not valid syntax. It would have to be YEAR('2018-01-01').
BTW your query is better written with between. Same thing, easier to read, less chance of bugs.
select * from foo where date1 between '2018-01-01' AND '2018-12-31';
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
You have a syntax error.
mysql> Select * from foo where date1 >= YEAR '2018-01-01' AND date1<= '2018-12-31' ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2018-01-01' AND date1<= '2018-12-31'' at line 1
YEAR '2018-01-01' is not valid syntax. It would have to be YEAR('2018-01-01').
BTW your query is better written with between. Same thing, easier to read, less chance of bugs.
select * from foo where date1 between '2018-01-01' AND '2018-12-31';
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
answered Nov 14 '18 at 21:51
SchwernSchwern
90.2k17102233
90.2k17102233
1
thank you it worked really appreciate your help
– a c
Nov 14 '18 at 21:59
add a comment |
1
thank you it worked really appreciate your help
– a c
Nov 14 '18 at 21:59
1
1
thank you it worked really appreciate your help
– a c
Nov 14 '18 at 21:59
thank you it worked really appreciate your help
– a c
Nov 14 '18 at 21:59
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%2f53309167%2ffilter-query-by-dates-mysql%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
Hi really appeciate your response its a longtext
– a c
Nov 14 '18 at 21:51
It is best practice to store date values as
dateordatetimedatatypes.– fubar
Nov 14 '18 at 21:53
I am sorry thank you for the advice really appreciate
– a c
Nov 14 '18 at 21:58