comparing dates

[dkin]

In my database I have a list of dates, laid out like so

m/d/y

now the variable that equals that would look like this

$today = date("n/j/Y");

so I am using explode to divide it into an array with the / but I am not to sure how I can compare them, what I want is if a date from the db is within the next 7 days I would like it displayed. So today is the 30 anything up to the 6th should be displayed, now, this prolly means I have to input all the days of all the months etc, but I have never written a script this complex before (on the surface it looked easy lol)

So if someone could point me in the right direction I would appreciate it.

[arran]

Hi dkin,

Sounds like you could use the

adddate()

function. Something like:

select * from yourTable where dateColumn >= '$theDate' and dateColumn < adddate('$theDate', INTERVAL 7 DAY);

arran.

[coopster]

That won't work unless the date is formatted correctly, though. Most DBMS with Date/Time functions expect any date column-types to be in a certain format, usually ISO (yyyy-mm-dd). You are always better off creating any date columns as type DATE so that you can use the DBMS Date/Time functions without having to jump through hoops to accomplish the task at hand. In this case, you are storing the date in a format that isn't going to lend itself too nicely to manipulation.

First, if you can, you should try to use the standard DATE column type and it's expected format. If you can't you are going to have to format the date prior to building the query statement in order to get the calculation to return the expected results.

As an example, here it is from the MySQL manual pages:

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

Just a heads up...

[dkin]

I can only have it this way, what it is is a release date for a gmae, say the game is released on 09/24/06. Any ideas on how I could do this?