Query to get all rows from previous month

Multi tool use
Multi tool use












68














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.










share|improve this question





























    68














    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.










    share|improve this question



























      68












      68








      68


      25





      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.










      share|improve this question















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Dec 27 at 13:27









      Braiam

      3,54693463




      3,54693463










      asked Jan 19 '10 at 0:26









      lewisqic

      80831419




      80831419
























          9 Answers
          9






          active

          oldest

          votes


















          152














          SELECT * FROM table
          WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
          AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)





          share|improve this answer

















          • 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 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




            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



















          16














          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' )





          share|improve this answer



















          • 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 a STR_TO_DATE around the DATE_FORMAT so it is always dealing with date objects.
            – Leigh
            Nov 13 '12 at 20:15



















          13














          If there are no future dates ...



          SELECT * 
          FROM table_name
          WHERE date_created > (NOW() - INTERVAL 1 MONTH);


          Tested.






          share|improve this answer



















          • 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



















          10














          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)





          share|improve this answer































            3














            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.






            share|improve this answer























            • 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



















            2














            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();





            share|improve this answer

















            • 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



















            2














            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






            share|improve this answer































              1














              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.






              share|improve this answer























              • @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



















              1














              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)






              share|improve this answer























                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
                });


                }
                });














                draft saved

                draft discarded


















                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









                152














                SELECT * FROM table
                WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)





                share|improve this answer

















                • 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 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




                  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
















                152














                SELECT * FROM table
                WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)





                share|improve this answer

















                • 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 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




                  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














                152












                152








                152






                SELECT * FROM table
                WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)





                share|improve this answer












                SELECT * FROM table
                WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)






                share|improve this answer












                share|improve this answer



                share|improve this answer










                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 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




                  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














                • 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 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




                  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








                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













                16














                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' )





                share|improve this answer



















                • 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 a STR_TO_DATE around the DATE_FORMAT so it is always dealing with date objects.
                  – Leigh
                  Nov 13 '12 at 20:15
















                16














                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' )





                share|improve this answer



















                • 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 a STR_TO_DATE around the DATE_FORMAT so it is always dealing with date objects.
                  – Leigh
                  Nov 13 '12 at 20:15














                16












                16








                16






                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' )





                share|improve this answer














                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' )






                share|improve this answer














                share|improve this answer



                share|improve this answer








                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 a STR_TO_DATE around the DATE_FORMAT so it is always dealing with date objects.
                  – Leigh
                  Nov 13 '12 at 20:15














                • 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 a STR_TO_DATE around the DATE_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











                13














                If there are no future dates ...



                SELECT * 
                FROM table_name
                WHERE date_created > (NOW() - INTERVAL 1 MONTH);


                Tested.






                share|improve this answer



















                • 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
















                13














                If there are no future dates ...



                SELECT * 
                FROM table_name
                WHERE date_created > (NOW() - INTERVAL 1 MONTH);


                Tested.






                share|improve this answer



















                • 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














                13












                13








                13






                If there are no future dates ...



                SELECT * 
                FROM table_name
                WHERE date_created > (NOW() - INTERVAL 1 MONTH);


                Tested.






                share|improve this answer














                If there are no future dates ...



                SELECT * 
                FROM table_name
                WHERE date_created > (NOW() - INTERVAL 1 MONTH);


                Tested.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                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














                • 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











                10














                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)





                share|improve this answer




























                  10














                  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)





                  share|improve this answer


























                    10












                    10








                    10






                    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)





                    share|improve this answer














                    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)






                    share|improve this answer














                    share|improve this answer



                    share|improve this answer








                    edited May 23 '17 at 12:10









                    Community

                    11




                    11










                    answered Jan 4 '13 at 21:31









                    SMTF

                    380615




                    380615























                        3














                        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.






                        share|improve this answer























                        • 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
















                        3














                        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.






                        share|improve this answer























                        • 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














                        3












                        3








                        3






                        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.






                        share|improve this answer














                        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.







                        share|improve this answer














                        share|improve this answer



                        share|improve this answer








                        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


















                        • 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











                        2














                        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();





                        share|improve this answer

















                        • 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
















                        2














                        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();





                        share|improve this answer

















                        • 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














                        2












                        2








                        2






                        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();





                        share|improve this answer












                        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();






                        share|improve this answer












                        share|improve this answer



                        share|improve this answer










                        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














                        • 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











                        2














                        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






                        share|improve this answer




























                          2














                          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






                          share|improve this answer


























                            2












                            2








                            2






                            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






                            share|improve this answer














                            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







                            share|improve this answer














                            share|improve this answer



                            share|improve this answer








                            edited Aug 7 '14 at 15:39









                            dda

                            5,44821932




                            5,44821932










                            answered Aug 7 '14 at 15:30









                            saqib jahangir Pakistan

                            211




                            211























                                1














                                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.






                                share|improve this answer























                                • @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
















                                1














                                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.






                                share|improve this answer























                                • @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














                                1












                                1








                                1






                                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.






                                share|improve this answer














                                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.







                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                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


















                                • @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











                                1














                                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)






                                share|improve this answer




























                                  1














                                  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)






                                  share|improve this answer


























                                    1












                                    1








                                    1






                                    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)






                                    share|improve this answer














                                    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)







                                    share|improve this answer














                                    share|improve this answer



                                    share|improve this answer








                                    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






























                                        draft saved

                                        draft discarded




















































                                        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.




                                        draft saved


                                        draft discarded














                                        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





















































                                        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
                                        sjoPsKlP5l,OQbY EGKMz5IPujApeBS5N8fZ 7sSmf6heuDvlx

                                        Popular posts from this blog

                                        Monofisismo

                                        Angular Downloading a file using contenturl with Basic Authentication

                                        Olmecas