MySQL DATE_FORMAT + PHP Time Comparison in Query

[drath]

MySQL Version: 5.0.32

 

Hello there guys, I've been stumped for awhile using DATE_FORMAT in a SELECT that I have been creating. Unfortunately the field is not in a timestamp, or a straightforward datetime format, and there is no ability to edit that, so it looks like it's DATE_FORMAT to the rescue... except it's not working (yet).

 

The field (lastlogin) is formatted as: July 24th, 2010 2:03 PM or in PHP: date('F jS, Y g:i A').

 

As far as I can tell, the MySQL equivalent of that is %M %D, %Y %l:%i %p.

 

This is my query for trying to select only the times within the last month:

 

$sql = "SELECT snip FROM snip WHERE DATE_FORMAT('lastlogin', '%M %D, %Y %l:%i %p') >= '" . date("F jS, Y g:i A", strtotime("-1 month")) . "' ORDER BY snip DESC LIMIT 20";

 

Can anybody spot what I may be doing wrong here? It is not returning any error, so I assume it is at least executing, but nothing is showing up, which means I made a formatting mistake possible? Thanks!

 

*EDIT* The PHP portion in there is confirmed working, it returns a date of the previous month in the proper format, "September 19th, 2011 6:39 PM" for example at the time of this post.

 

[Pikachu2000]

Dates should be stored in YYYY-MM-DD HH:MM:SS format, in a DATETIME type field in the database so you can do these comparisons easily. The way you have the date stored is not going to be easy to work with at all.

[drath]

I would have the date stored in DATETIME/TIMESTAMP as noted if I had a choice

[Pikachu2000]

Whoever set it up that way should have their testicles forcibly removed; if you can get someone to change it, you'd be much better off to do so. You cant do a greater than / less than comparison on a date in that format. You're going to need to use MySQ's STR_TO_DATE() to format the date string into a valid YYYY-MM-DD HH:MM:SS format, then use DATE_SUB() to get the value for the comparison.

[drath]

Thanks, I was not even aware of STR_TO_DATE(). I will take a look at this and try to come up with something. Thanks!

[drath]

For posterity sake, here is the final query I used to get this working:

 

WHERE STR_TO_DATE(lastlogin, '%M %D, %Y %l:%i %p') >= DATE_SUB(NOW(), INTERVAL 1 MONTH)

 

I thought I used to be able to mark the thread as solved? Either way, it's SOLVED

[Pikachu2000]

The topic solved button is down below, but it isn't green anymore . . .