[RESOLVED] MySQL date help

Rodney_H_

Hi,

I am querying the db to pull out all the actions.

<?php
$query = "SELECT * FROM `actions`";
?>

Works great. But, how could I change (add) my WHERE clause to only pull out actions where the date column is today OR in the future, but not yet passed?

<?php
$query = "SELECT * FROM `actions` WHERE `action_start_date` >= now()"; // ?
?>

The action_start_date is the content type: date, and has records stored like: 2006-03-01

I searched MySQL date functions, but couldn't figure it out. Any help or guidance would be appreciated.

jkurrle

Why not create a PHP variable set to today's date, then use the variable in your where clause?

Rodney_H_

Yes, I can create the variable, but HOW do I use it in the WHERE clause?

How would I structure the query?? That is the bit that I can't seem to figure out.

jkurrle

Let's see...

$this_date=time();
$today=date("Y-m-d",$this_date);
$query = "SELECT * FROM `actions` 
  WHERE `action_start_date` >= \"$today\" ";

Give this a try

Rodney_H_

Hi, jkurrle:

I tried this, but the results returned were not accurrate.

Can you just use the mathematical operators (greater than, less than, and equal to) for evaluating dates?

Aren't there built in MySQL date functions that would be more optimal to use in this case? When the data is already stored in a date-type field??

Confused.................... (??)

jkurrle

How about this:

$query = "SELECT * FROM `actions` 
  WHERE `action_start_date` >= CURRENT_DATE() ";

Roger_Ramjet

Rodney H. wrote:
Hi, jkurrle:

Aren't there built in MySQL date functions that would be more optimal to use in this case? When the data is already stored in a date-type field??

Confused.................... (??)

RTFM

:mad: :mad: :mad:

Rodney_H_

Roger:

There is no need to be mad. Did you read my first post that says I DID read the manual, and that is why I am confused: THere are so many functions that I literally don't know which one to use.

I was hoping that someone could point me in the right direction. Not scorn me for asking for help! I will go back and read it again, to see if I can figure it out, but honest to gosh, I did once already...

Roger_Ramjet

Well then you must have read this - it's at the top of the page

Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:

mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

Note that the query also selects rows with dates that lie in the future.

It answers all your questions:

Yes, you can use the normal comparison operators: ) <= date_col
Yes, there is an inbuilt function for today's date. CURDATE()

And, no I'm not mad, just bored with people who give up too easily. Your query in your first post was nearly right, why did you not examine it closer? NOW() return date and time, your column just stores date, think about it.

Rodney_H_

Hi, Roger.

I appreciate you pointing me back to the mySQL manual. (For some reason I totally understand the PHP manual, but have been lost on more than one occassion on MySQL's site, and DID give up too easily in this case. You are right.)

So, I went back to the manual and forced myself to read it. I mean, turn off the radio and concentrate type of read... and, LOL, understood it much better this way.

I did bump head long into that first example: DOH!

And also learned that:

// MySQL automatically converts a date or time type value to a number if the value is used in a numeric context and vice versa.

// When MySQL encounters a value for a date or time type that is illegal, it converts the value to the 'zero' value for that type.

So the things I were trying were correct, but because my values/dates were made up for testing only, my results were not accurrate cos MySQL was converting the illegals to zero! Double-Doh!

Thanks for encouraging me not to take the easy way out. I do appreciate it, even if I seemed defensive at first.

Roger_Ramjet

That's the spirit. Glad to help.