By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,831 Members | 2,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,831 IT Pros & Developers. It's quick & easy.

MySQL Date Field Help Needed

P: n/a
I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

....doesn't do anything...

Thanks for any help.

Jul 16 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
* Ralph Freshour <ra***@primemail.com>:
I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

...doesn't do anything...


I'm not too familiar with date calculations in MySQL, but perhaps changing
"creation_date =" to "creation_date >=" will solve your problem.

--
Benjamin D. Esham { http://bdesham.net
bd*****@iname.com } AIM: bdesham 1 2 8
"Hmm... Earl Grey tea. Maybe that's how an old
bald guy can kick the Borg's collective ass."
-- bsharitt on Slashdot

Jul 16 '05 #2

P: n/a
No, that didn't work...any other thoughts???
On Sun, 31 Aug 2003 22:35:55 -0400, Benjamin Esham <bd*****@iname.com>
wrote:
* Ralph Freshour <ra***@primemail.com>:
I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

...doesn't do anything...


I'm not too familiar with date calculations in MySQL, but perhaps changing
"creation_date =" to "creation_date >=" will solve your problem.


Jul 16 '05 #3

P: n/a
Ralph Freshour <ra***@primemail.com> wrote:
I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";


You're searching rows which are exactly 30 days old - if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date, INTERVAL
-30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date, INTERVAL
30 DAY);

HTH;
JOn
Jul 16 '05 #4

P: n/a
Hi Jon -

I tried both of your suggestions but neither worked - I doubled
checked my MySQL table col (field) data and all the dates are of Date
type and they are all in the month of August 2003 (varying days; 5th,
10th, etc.)

I don't have to tell the query to compare the last 30 days from today?
That's assumed in the query right?
On Mon, 01 Sep 2003 10:24:33 +0100, Jon Kraft <jo*@jonux.co.uk> wrote:
Ralph Freshour <ra***@primemail.com> wrote:
I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";


You're searching rows which are exactly 30 days old - if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date, INTERVAL
-30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date, INTERVAL
30 DAY);

HTH;
JOn


Jul 16 '05 #5

P: n/a
Ralph Freshour <ra***@primemail.com> wrote:
On Mon, 01 Sep 2003 10:24:33 +0100, Jon Kraft <jo*@jonux.co.uk> wrote:
Ralph Freshour <ra***@primemail.com> wrote:
I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";


You're searching rows which are exactly 30 days old - if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date,
INTERVAL -30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date,
INTERVAL 30 DAY);


I tried both of your suggestions but neither worked - I doubled
checked my MySQL table col (field) data and all the dates are of Date
type and they are all in the month of August 2003 (varying days; 5th,
10th, etc.)

I don't have to tell the query to compare the last 30 days from today?
That's assumed in the query right?


Hi Ralph,

Sorry about that, you're absolutely right, you have to tell the period of
days from now, not from the datefield!

SELECT * FROM basics WHERE creation_date > DATE_SUB(NOW(), INTERVAL 30 DAY);

HTH;
JOn
Jul 16 '05 #6

P: n/a
On Sun, 31 Aug 2003 20:05:30 -0500, in message
<qk********************************@4ax.com>, the AI program named "Ralph
Freshour" <ra***@primemail.com> randomly printed:
I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

...doesn't do anything...

Thanks for any help.


Untested:

select * from basics where creation_date >=
date_add(now(), interval -30 day);

Jul 16 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.