sorting by and using field that contains dates for subtraction ...

[Jax2]

Hi all, I'm working on a project and running into a bit of trouble.

 

I have a field in the database that inserts a date generated by date('Y-m-d');, in other words, today would show up as 2010-04-08 ...

 

I am trying to figure out a few things... for one, I can't seem to sort by this field. I'd like to sort it with the newest record first, down to the oldest, so I tried ORDER BY date DESC and it doesn't change anything.

 

Second, I need to find ONLY the records where the date is previous to todays date, so I tried using this:

 

$today=date('Y-m-d');

 

and then in the query  WHERE date < $today ... which is ALSO not working.

 

Suggestions?

 

[JAY6390]

You should use the date or datetime field type, and use the built in functions of mysql for querying with dates

[Jax2]

I know it should have been done that way in the beginning, but they've already built their entire website around this as is ... So I'm pretty much stuck trying to figure out a way to use a string such as 2010-04-01 and finding out if that string is a date earlier than todays date of 2010-04-08 (which of course it is) ...

[JAY6390]

In that case use this function to create a date from the string

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

then use that with the functions provided

[PFMaBiSmAd]

Both of the the things you are trying should work (even if the dates in that format are stored as strings.)

 

You would need to show us the definition of that column (just in case), show the actual data values that are not producing the expected results, show the actual query statements, show the code that is producing the incorrect results, and show the results you are getting.

 

Also, it is never too late to fix a design problem. If the values in the current field are in fact of the format that you have shown, you can simply alter the column data type and the values will be carried over (make sure you have a known good backup of the database first.) If the values are actually something else, you would need to add a new column of the correct type, populate it from the existing values (there are several methods that can be used to do this depending on the actual starting format), check that the values actually carried over, remove the old column, and rename the new column as the old one.

 

 

[Jax2]

Okay, let's see...

 

contact_next field is varchar, 100 ...

 

the date is selected in a form like this:

 

<option value="<?php echo date('Y-m-d', strtotime('+1 day')); ?>">1 Day</option>
<option value="<?php echo date('Y-m-d', strtotime('+2 day')); ?>">2 Days</option>
<option value="<?php echo date('Y-m-d', strtotime('+3 day')); ?>">3 Days</option>
...etc

 

on the processing page, it simply sanitizes the $contact_next and adds it to the database normally (insert into ... values ('$contact_next') ...

 

so I am left with a varchar string such as 2010-04-08

 

So what I did was create a new date stamp above the query which reads as follows:

 

$today=date('Y-m-d');

mysql_query("SELECT *,UNIX_TIMESTAMP(tstamp) AS tstamp FROM prospects WHERE contact_next < $today ORDER BY contact_next DESC ");

 

It is returning records, but all of the dates it is returning are in the future, and its also not sorting the records by contact_next desc as I am trying to get it to.

 

can you subtract a string (2010-04-01) from a date (2010-04-08)?

 

I looked into str2date function and I can't make heads or tails out of it or how it will help me.