finding spec date

[yoko]

Hi

 

I have a table with columns name, user, date, comment.

 

How can i get the last month entries?

I mean a code that always shows the last month entries.

 

Can i do it without using PHP codes and only MySQL codes?

[fenway]

Assuming your date column is a DATE type, you can use "yourDateColumn >= CURDATE() - INTERVAL 1 MONTH".

 

Of course, that depends on your definiton of "last" and "month".

[yoko]

Thanks for reply.

Today is 2008/4/28. Last month would be from 2008/3/1 to 2008/4/1.

So until we reach 2008/5/1, the last month will be still  from 2008/3/1 to 2008/4/1.

 

Thanks

[fenway]

Well, then you'll have to do some fun date math / manipulation to get the first day... get the current year/month, go back one, add the date part ('01'), and then use INTERVAL and BETWEEN.

[PFMaBiSmAd]

I am guessing that the end dates shown in the example (the first day of the next month) are not inclusive? If so, the following should work (untested and is probably not the only or quickest way) -

 

SELECT your_columns FROM your_table WHERE
PERIOD_ADD(REPLACE(LEFT(CURDATE(),7),'-',''),-1) = REPLACE(LEFT(your_date_column,7),'-','')

 

The period_add function takes care of year/month roll-over/roll-under.

[fenway]

You need a range... an equality won't work..?

[PFMaBiSmAd]

It's comparing the yyyymm one period (month) before the current date with the yyyymm from the date in the table. No range is necessary.

[fenway]

It's comparing the yyyymm one period (month) before the current date with the yyyymm from the date in the table. No range is necessary.

Sorry, I glanced over the period function... interesting approach.