Query to get all rows from previous month

Multi tool use
I need to select all rows in my database that were created last month.
For example, if the current month is January, then I want to return all rows that were created in December, if the month is February, then I want to return all rows that were created in January. I have a date_created
column in my database that lists the date created in this format: 2007-06-05 14:50:17
.
mysql
add a comment |
I need to select all rows in my database that were created last month.
For example, if the current month is January, then I want to return all rows that were created in December, if the month is February, then I want to return all rows that were created in January. I have a date_created
column in my database that lists the date created in this format: 2007-06-05 14:50:17
.
mysql
add a comment |
I need to select all rows in my database that were created last month.
For example, if the current month is January, then I want to return all rows that were created in December, if the month is February, then I want to return all rows that were created in January. I have a date_created
column in my database that lists the date created in this format: 2007-06-05 14:50:17
.
mysql
I need to select all rows in my database that were created last month.
For example, if the current month is January, then I want to return all rows that were created in December, if the month is February, then I want to return all rows that were created in January. I have a date_created
column in my database that lists the date created in this format: 2007-06-05 14:50:17
.
mysql
mysql
edited Dec 27 at 13:27
Braiam
3,54693463
3,54693463
asked Jan 19 '10 at 0:26
lewisqic
80831419
80831419
add a comment |
add a comment |
9 Answers
9
active
oldest
votes
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
49
SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
– Ghazanfar Mir
Jun 27 '13 at 9:34
4
@GhazanfarMir your query doesn't quite match the question, whereas the answer above, does. Yours returns the rows between this day last month through yesterday.
– Matt Passell
Jan 29 '14 at 18:45
@MattPassell I am sorry butbetween
is inclusive of the boundaries.. It will include current date as well. Why/How it will be limited to yesterday?? Please elaborate?
– Ghazanfar Mir
Jan 30 '14 at 10:26
1
I see what you mean.. Yes you are right.. my query isn't what OP wanted.. I misunderstood the question
– Ghazanfar Mir
Jan 30 '14 at 10:29
what about if thecurrent_date
it is not today but a random date given from the client?
– dios231
Sep 29 '16 at 15:53
|
show 1 more comment
Here's another alternative. Assuming you have an indexed DATE
or DATETIME
type field, this should use the index as the formatted dates will be type converted before the index is used. You should then see a range
query rather than an index
query when viewed with EXPLAIN.
SELECT
*
FROM
table
WHERE
date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
1
this will get you the records which were created on the first day of the current month though.
– ggiroux
Jan 19 '10 at 0:54
@ggiroux - Indeed. Thanks - fixed.
– martin clayton
Jan 19 '10 at 0:56
+1 then - much cleaner than mine :)
– ggiroux
Jan 19 '10 at 0:57
+1. Much more index friendly. You could also throw aSTR_TO_DATE
around theDATE_FORMAT
so it is always dealing with date objects.
– Leigh
Nov 13 '12 at 20:15
add a comment |
If there are no future dates ...
SELECT *
FROM table_name
WHERE date_created > (NOW() - INTERVAL 1 MONTH);
Tested.
1
I think they were looking for something different. ie All records from last month (ie Oct 1 - Oct 31, 2012 23:59:59). The query above would return the last ~30 or so days, from today's date and time.
– Leigh
Nov 13 '12 at 20:24
add a comment |
Alternatively to hobodave's answer
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
You could achieve the same with EXTRACT, using YEAR_MONTH as unit, thus you wouldn't need the AND, like so:
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)
add a comment |
SELECT *
FROM yourtable
where DATE_FORMAT(date_created, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 month),'%Y-%m')
This should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
I forgot to mention...this should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
– Gregg
Mar 2 '17 at 3:44
In my opinion this should be the accepted answer as it is simpler than the accepted answer yet provides the same result
– Grant
Mar 9 at 9:56
add a comment |
Even though the answer for this question has been selected already, however, I believe the simplest query will be
SELECT *
FROM table
WHERE
date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
1
No, not quite. See my response to your comment on the answer given by @hobodave
– Matt Passell
Jan 29 '14 at 18:47
@MattPassell You are right, I misunderstood the question.. I missed the point that result should be only limited to records ONLY from last month.
– Ghazanfar Mir
Jan 30 '14 at 10:31
add a comment |
Here is the query to get the records of the last month:
SELECT *
FROM `tablename`
WHERE `datefiled`
BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH )
AND
LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH ) )
Regards
- saqib
add a comment |
select fields FROM table
WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');
this one will be able to take advantage of an index if your date_created is indexed, because it doesn't apply any transformation function to the field value.
@ggiroux - It does have to convert the date to a character type before applying the LIKE though.
– martin clayton
Jan 19 '10 at 0:51
yes indeed, but still an improvement over the selected answer IMHO (IFF date_created is indexed)
– ggiroux
Jan 19 '10 at 1:08
add a comment |
WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY)
This worked for me (Selects all records created from last month, regardless of the day you run the query this month)
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%2f2090221%2fquery-to-get-all-rows-from-previous-month%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
9 Answers
9
active
oldest
votes
9 Answers
9
active
oldest
votes
active
oldest
votes
active
oldest
votes
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
49
SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
– Ghazanfar Mir
Jun 27 '13 at 9:34
4
@GhazanfarMir your query doesn't quite match the question, whereas the answer above, does. Yours returns the rows between this day last month through yesterday.
– Matt Passell
Jan 29 '14 at 18:45
@MattPassell I am sorry butbetween
is inclusive of the boundaries.. It will include current date as well. Why/How it will be limited to yesterday?? Please elaborate?
– Ghazanfar Mir
Jan 30 '14 at 10:26
1
I see what you mean.. Yes you are right.. my query isn't what OP wanted.. I misunderstood the question
– Ghazanfar Mir
Jan 30 '14 at 10:29
what about if thecurrent_date
it is not today but a random date given from the client?
– dios231
Sep 29 '16 at 15:53
|
show 1 more comment
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
49
SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
– Ghazanfar Mir
Jun 27 '13 at 9:34
4
@GhazanfarMir your query doesn't quite match the question, whereas the answer above, does. Yours returns the rows between this day last month through yesterday.
– Matt Passell
Jan 29 '14 at 18:45
@MattPassell I am sorry butbetween
is inclusive of the boundaries.. It will include current date as well. Why/How it will be limited to yesterday?? Please elaborate?
– Ghazanfar Mir
Jan 30 '14 at 10:26
1
I see what you mean.. Yes you are right.. my query isn't what OP wanted.. I misunderstood the question
– Ghazanfar Mir
Jan 30 '14 at 10:29
what about if thecurrent_date
it is not today but a random date given from the client?
– dios231
Sep 29 '16 at 15:53
|
show 1 more comment
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
answered Jan 19 '10 at 0:28
hobodave
24.1k46373
24.1k46373
49
SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
– Ghazanfar Mir
Jun 27 '13 at 9:34
4
@GhazanfarMir your query doesn't quite match the question, whereas the answer above, does. Yours returns the rows between this day last month through yesterday.
– Matt Passell
Jan 29 '14 at 18:45
@MattPassell I am sorry butbetween
is inclusive of the boundaries.. It will include current date as well. Why/How it will be limited to yesterday?? Please elaborate?
– Ghazanfar Mir
Jan 30 '14 at 10:26
1
I see what you mean.. Yes you are right.. my query isn't what OP wanted.. I misunderstood the question
– Ghazanfar Mir
Jan 30 '14 at 10:29
what about if thecurrent_date
it is not today but a random date given from the client?
– dios231
Sep 29 '16 at 15:53
|
show 1 more comment
49
SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
– Ghazanfar Mir
Jun 27 '13 at 9:34
4
@GhazanfarMir your query doesn't quite match the question, whereas the answer above, does. Yours returns the rows between this day last month through yesterday.
– Matt Passell
Jan 29 '14 at 18:45
@MattPassell I am sorry butbetween
is inclusive of the boundaries.. It will include current date as well. Why/How it will be limited to yesterday?? Please elaborate?
– Ghazanfar Mir
Jan 30 '14 at 10:26
1
I see what you mean.. Yes you are right.. my query isn't what OP wanted.. I misunderstood the question
– Ghazanfar Mir
Jan 30 '14 at 10:29
what about if thecurrent_date
it is not today but a random date given from the client?
– dios231
Sep 29 '16 at 15:53
49
49
SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
– Ghazanfar Mir
Jun 27 '13 at 9:34
SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
– Ghazanfar Mir
Jun 27 '13 at 9:34
4
4
@GhazanfarMir your query doesn't quite match the question, whereas the answer above, does. Yours returns the rows between this day last month through yesterday.
– Matt Passell
Jan 29 '14 at 18:45
@GhazanfarMir your query doesn't quite match the question, whereas the answer above, does. Yours returns the rows between this day last month through yesterday.
– Matt Passell
Jan 29 '14 at 18:45
@MattPassell I am sorry but
between
is inclusive of the boundaries.. It will include current date as well. Why/How it will be limited to yesterday?? Please elaborate?– Ghazanfar Mir
Jan 30 '14 at 10:26
@MattPassell I am sorry but
between
is inclusive of the boundaries.. It will include current date as well. Why/How it will be limited to yesterday?? Please elaborate?– Ghazanfar Mir
Jan 30 '14 at 10:26
1
1
I see what you mean.. Yes you are right.. my query isn't what OP wanted.. I misunderstood the question
– Ghazanfar Mir
Jan 30 '14 at 10:29
I see what you mean.. Yes you are right.. my query isn't what OP wanted.. I misunderstood the question
– Ghazanfar Mir
Jan 30 '14 at 10:29
what about if the
current_date
it is not today but a random date given from the client?– dios231
Sep 29 '16 at 15:53
what about if the
current_date
it is not today but a random date given from the client?– dios231
Sep 29 '16 at 15:53
|
show 1 more comment
Here's another alternative. Assuming you have an indexed DATE
or DATETIME
type field, this should use the index as the formatted dates will be type converted before the index is used. You should then see a range
query rather than an index
query when viewed with EXPLAIN.
SELECT
*
FROM
table
WHERE
date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
1
this will get you the records which were created on the first day of the current month though.
– ggiroux
Jan 19 '10 at 0:54
@ggiroux - Indeed. Thanks - fixed.
– martin clayton
Jan 19 '10 at 0:56
+1 then - much cleaner than mine :)
– ggiroux
Jan 19 '10 at 0:57
+1. Much more index friendly. You could also throw aSTR_TO_DATE
around theDATE_FORMAT
so it is always dealing with date objects.
– Leigh
Nov 13 '12 at 20:15
add a comment |
Here's another alternative. Assuming you have an indexed DATE
or DATETIME
type field, this should use the index as the formatted dates will be type converted before the index is used. You should then see a range
query rather than an index
query when viewed with EXPLAIN.
SELECT
*
FROM
table
WHERE
date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
1
this will get you the records which were created on the first day of the current month though.
– ggiroux
Jan 19 '10 at 0:54
@ggiroux - Indeed. Thanks - fixed.
– martin clayton
Jan 19 '10 at 0:56
+1 then - much cleaner than mine :)
– ggiroux
Jan 19 '10 at 0:57
+1. Much more index friendly. You could also throw aSTR_TO_DATE
around theDATE_FORMAT
so it is always dealing with date objects.
– Leigh
Nov 13 '12 at 20:15
add a comment |
Here's another alternative. Assuming you have an indexed DATE
or DATETIME
type field, this should use the index as the formatted dates will be type converted before the index is used. You should then see a range
query rather than an index
query when viewed with EXPLAIN.
SELECT
*
FROM
table
WHERE
date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
Here's another alternative. Assuming you have an indexed DATE
or DATETIME
type field, this should use the index as the formatted dates will be type converted before the index is used. You should then see a range
query rather than an index
query when viewed with EXPLAIN.
SELECT
*
FROM
table
WHERE
date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
edited Jan 19 '10 at 0:56
answered Jan 19 '10 at 0:48


martin clayton
65.1k17186179
65.1k17186179
1
this will get you the records which were created on the first day of the current month though.
– ggiroux
Jan 19 '10 at 0:54
@ggiroux - Indeed. Thanks - fixed.
– martin clayton
Jan 19 '10 at 0:56
+1 then - much cleaner than mine :)
– ggiroux
Jan 19 '10 at 0:57
+1. Much more index friendly. You could also throw aSTR_TO_DATE
around theDATE_FORMAT
so it is always dealing with date objects.
– Leigh
Nov 13 '12 at 20:15
add a comment |
1
this will get you the records which were created on the first day of the current month though.
– ggiroux
Jan 19 '10 at 0:54
@ggiroux - Indeed. Thanks - fixed.
– martin clayton
Jan 19 '10 at 0:56
+1 then - much cleaner than mine :)
– ggiroux
Jan 19 '10 at 0:57
+1. Much more index friendly. You could also throw aSTR_TO_DATE
around theDATE_FORMAT
so it is always dealing with date objects.
– Leigh
Nov 13 '12 at 20:15
1
1
this will get you the records which were created on the first day of the current month though.
– ggiroux
Jan 19 '10 at 0:54
this will get you the records which were created on the first day of the current month though.
– ggiroux
Jan 19 '10 at 0:54
@ggiroux - Indeed. Thanks - fixed.
– martin clayton
Jan 19 '10 at 0:56
@ggiroux - Indeed. Thanks - fixed.
– martin clayton
Jan 19 '10 at 0:56
+1 then - much cleaner than mine :)
– ggiroux
Jan 19 '10 at 0:57
+1 then - much cleaner than mine :)
– ggiroux
Jan 19 '10 at 0:57
+1. Much more index friendly. You could also throw a
STR_TO_DATE
around the DATE_FORMAT
so it is always dealing with date objects.– Leigh
Nov 13 '12 at 20:15
+1. Much more index friendly. You could also throw a
STR_TO_DATE
around the DATE_FORMAT
so it is always dealing with date objects.– Leigh
Nov 13 '12 at 20:15
add a comment |
If there are no future dates ...
SELECT *
FROM table_name
WHERE date_created > (NOW() - INTERVAL 1 MONTH);
Tested.
1
I think they were looking for something different. ie All records from last month (ie Oct 1 - Oct 31, 2012 23:59:59). The query above would return the last ~30 or so days, from today's date and time.
– Leigh
Nov 13 '12 at 20:24
add a comment |
If there are no future dates ...
SELECT *
FROM table_name
WHERE date_created > (NOW() - INTERVAL 1 MONTH);
Tested.
1
I think they were looking for something different. ie All records from last month (ie Oct 1 - Oct 31, 2012 23:59:59). The query above would return the last ~30 or so days, from today's date and time.
– Leigh
Nov 13 '12 at 20:24
add a comment |
If there are no future dates ...
SELECT *
FROM table_name
WHERE date_created > (NOW() - INTERVAL 1 MONTH);
Tested.
If there are no future dates ...
SELECT *
FROM table_name
WHERE date_created > (NOW() - INTERVAL 1 MONTH);
Tested.
edited Nov 13 '12 at 20:16


Leigh
27.6k74484
27.6k74484
answered Apr 21 '11 at 17:14
ekerner
4,12612623
4,12612623
1
I think they were looking for something different. ie All records from last month (ie Oct 1 - Oct 31, 2012 23:59:59). The query above would return the last ~30 or so days, from today's date and time.
– Leigh
Nov 13 '12 at 20:24
add a comment |
1
I think they were looking for something different. ie All records from last month (ie Oct 1 - Oct 31, 2012 23:59:59). The query above would return the last ~30 or so days, from today's date and time.
– Leigh
Nov 13 '12 at 20:24
1
1
I think they were looking for something different. ie All records from last month (ie Oct 1 - Oct 31, 2012 23:59:59). The query above would return the last ~30 or so days, from today's date and time.
– Leigh
Nov 13 '12 at 20:24
I think they were looking for something different. ie All records from last month (ie Oct 1 - Oct 31, 2012 23:59:59). The query above would return the last ~30 or so days, from today's date and time.
– Leigh
Nov 13 '12 at 20:24
add a comment |
Alternatively to hobodave's answer
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
You could achieve the same with EXTRACT, using YEAR_MONTH as unit, thus you wouldn't need the AND, like so:
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)
add a comment |
Alternatively to hobodave's answer
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
You could achieve the same with EXTRACT, using YEAR_MONTH as unit, thus you wouldn't need the AND, like so:
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)
add a comment |
Alternatively to hobodave's answer
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
You could achieve the same with EXTRACT, using YEAR_MONTH as unit, thus you wouldn't need the AND, like so:
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)
Alternatively to hobodave's answer
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
You could achieve the same with EXTRACT, using YEAR_MONTH as unit, thus you wouldn't need the AND, like so:
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)
edited May 23 '17 at 12:10
Community♦
11
11
answered Jan 4 '13 at 21:31
SMTF
380615
380615
add a comment |
add a comment |
SELECT *
FROM yourtable
where DATE_FORMAT(date_created, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 month),'%Y-%m')
This should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
I forgot to mention...this should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
– Gregg
Mar 2 '17 at 3:44
In my opinion this should be the accepted answer as it is simpler than the accepted answer yet provides the same result
– Grant
Mar 9 at 9:56
add a comment |
SELECT *
FROM yourtable
where DATE_FORMAT(date_created, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 month),'%Y-%m')
This should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
I forgot to mention...this should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
– Gregg
Mar 2 '17 at 3:44
In my opinion this should be the accepted answer as it is simpler than the accepted answer yet provides the same result
– Grant
Mar 9 at 9:56
add a comment |
SELECT *
FROM yourtable
where DATE_FORMAT(date_created, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 month),'%Y-%m')
This should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
SELECT *
FROM yourtable
where DATE_FORMAT(date_created, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 month),'%Y-%m')
This should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
edited Jan 10 at 14:16


Martijn
11.8k31954
11.8k31954
answered Mar 1 '17 at 11:16
Gregg
311
311
I forgot to mention...this should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
– Gregg
Mar 2 '17 at 3:44
In my opinion this should be the accepted answer as it is simpler than the accepted answer yet provides the same result
– Grant
Mar 9 at 9:56
add a comment |
I forgot to mention...this should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
– Gregg
Mar 2 '17 at 3:44
In my opinion this should be the accepted answer as it is simpler than the accepted answer yet provides the same result
– Grant
Mar 9 at 9:56
I forgot to mention...this should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
– Gregg
Mar 2 '17 at 3:44
I forgot to mention...this should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
– Gregg
Mar 2 '17 at 3:44
In my opinion this should be the accepted answer as it is simpler than the accepted answer yet provides the same result
– Grant
Mar 9 at 9:56
In my opinion this should be the accepted answer as it is simpler than the accepted answer yet provides the same result
– Grant
Mar 9 at 9:56
add a comment |
Even though the answer for this question has been selected already, however, I believe the simplest query will be
SELECT *
FROM table
WHERE
date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
1
No, not quite. See my response to your comment on the answer given by @hobodave
– Matt Passell
Jan 29 '14 at 18:47
@MattPassell You are right, I misunderstood the question.. I missed the point that result should be only limited to records ONLY from last month.
– Ghazanfar Mir
Jan 30 '14 at 10:31
add a comment |
Even though the answer for this question has been selected already, however, I believe the simplest query will be
SELECT *
FROM table
WHERE
date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
1
No, not quite. See my response to your comment on the answer given by @hobodave
– Matt Passell
Jan 29 '14 at 18:47
@MattPassell You are right, I misunderstood the question.. I missed the point that result should be only limited to records ONLY from last month.
– Ghazanfar Mir
Jan 30 '14 at 10:31
add a comment |
Even though the answer for this question has been selected already, however, I believe the simplest query will be
SELECT *
FROM table
WHERE
date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
Even though the answer for this question has been selected already, however, I believe the simplest query will be
SELECT *
FROM table
WHERE
date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
answered Jun 27 '13 at 9:35


Ghazanfar Mir
2,65411638
2,65411638
1
No, not quite. See my response to your comment on the answer given by @hobodave
– Matt Passell
Jan 29 '14 at 18:47
@MattPassell You are right, I misunderstood the question.. I missed the point that result should be only limited to records ONLY from last month.
– Ghazanfar Mir
Jan 30 '14 at 10:31
add a comment |
1
No, not quite. See my response to your comment on the answer given by @hobodave
– Matt Passell
Jan 29 '14 at 18:47
@MattPassell You are right, I misunderstood the question.. I missed the point that result should be only limited to records ONLY from last month.
– Ghazanfar Mir
Jan 30 '14 at 10:31
1
1
No, not quite. See my response to your comment on the answer given by @hobodave
– Matt Passell
Jan 29 '14 at 18:47
No, not quite. See my response to your comment on the answer given by @hobodave
– Matt Passell
Jan 29 '14 at 18:47
@MattPassell You are right, I misunderstood the question.. I missed the point that result should be only limited to records ONLY from last month.
– Ghazanfar Mir
Jan 30 '14 at 10:31
@MattPassell You are right, I misunderstood the question.. I missed the point that result should be only limited to records ONLY from last month.
– Ghazanfar Mir
Jan 30 '14 at 10:31
add a comment |
Here is the query to get the records of the last month:
SELECT *
FROM `tablename`
WHERE `datefiled`
BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH )
AND
LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH ) )
Regards
- saqib
add a comment |
Here is the query to get the records of the last month:
SELECT *
FROM `tablename`
WHERE `datefiled`
BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH )
AND
LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH ) )
Regards
- saqib
add a comment |
Here is the query to get the records of the last month:
SELECT *
FROM `tablename`
WHERE `datefiled`
BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH )
AND
LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH ) )
Regards
- saqib
Here is the query to get the records of the last month:
SELECT *
FROM `tablename`
WHERE `datefiled`
BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH )
AND
LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH ) )
Regards
- saqib
edited Aug 7 '14 at 15:39
dda
5,44821932
5,44821932
answered Aug 7 '14 at 15:30
saqib jahangir Pakistan
211
211
add a comment |
add a comment |
select fields FROM table
WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');
this one will be able to take advantage of an index if your date_created is indexed, because it doesn't apply any transformation function to the field value.
@ggiroux - It does have to convert the date to a character type before applying the LIKE though.
– martin clayton
Jan 19 '10 at 0:51
yes indeed, but still an improvement over the selected answer IMHO (IFF date_created is indexed)
– ggiroux
Jan 19 '10 at 1:08
add a comment |
select fields FROM table
WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');
this one will be able to take advantage of an index if your date_created is indexed, because it doesn't apply any transformation function to the field value.
@ggiroux - It does have to convert the date to a character type before applying the LIKE though.
– martin clayton
Jan 19 '10 at 0:51
yes indeed, but still an improvement over the selected answer IMHO (IFF date_created is indexed)
– ggiroux
Jan 19 '10 at 1:08
add a comment |
select fields FROM table
WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');
this one will be able to take advantage of an index if your date_created is indexed, because it doesn't apply any transformation function to the field value.
select fields FROM table
WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');
this one will be able to take advantage of an index if your date_created is indexed, because it doesn't apply any transformation function to the field value.
edited Jan 19 '10 at 0:50
answered Jan 19 '10 at 0:36
ggiroux
5,32211522
5,32211522
@ggiroux - It does have to convert the date to a character type before applying the LIKE though.
– martin clayton
Jan 19 '10 at 0:51
yes indeed, but still an improvement over the selected answer IMHO (IFF date_created is indexed)
– ggiroux
Jan 19 '10 at 1:08
add a comment |
@ggiroux - It does have to convert the date to a character type before applying the LIKE though.
– martin clayton
Jan 19 '10 at 0:51
yes indeed, but still an improvement over the selected answer IMHO (IFF date_created is indexed)
– ggiroux
Jan 19 '10 at 1:08
@ggiroux - It does have to convert the date to a character type before applying the LIKE though.
– martin clayton
Jan 19 '10 at 0:51
@ggiroux - It does have to convert the date to a character type before applying the LIKE though.
– martin clayton
Jan 19 '10 at 0:51
yes indeed, but still an improvement over the selected answer IMHO (IFF date_created is indexed)
– ggiroux
Jan 19 '10 at 1:08
yes indeed, but still an improvement over the selected answer IMHO (IFF date_created is indexed)
– ggiroux
Jan 19 '10 at 1:08
add a comment |
WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY)
This worked for me (Selects all records created from last month, regardless of the day you run the query this month)
add a comment |
WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY)
This worked for me (Selects all records created from last month, regardless of the day you run the query this month)
add a comment |
WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY)
This worked for me (Selects all records created from last month, regardless of the day you run the query this month)
WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY)
This worked for me (Selects all records created from last month, regardless of the day you run the query this month)
edited Jul 15 '16 at 15:05


Kurt Van den Branden
4,55563052
4,55563052
answered Jul 15 '16 at 13:52
Giles
111
111
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f2090221%2fquery-to-get-all-rows-from-previous-month%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
AJMgJYbOLHR5e5K3mjmRFYMN878x fH0,q5cZ7kD 8sYw