=> now (date query)

[twcmad]

Hi, This has been a pain in the bum fo rme, and I am sure it is simple,

I am wanting to call from a db entries that are greater than or equal to todays date.

I have been messing around with

$date = date(d m);

// in db  adding a field (var)  that carries the date in d m format

"SELECT * FROM whatever WHERE var => '$date'"

Is there an easy way in php to say where $value(some date format) => today(now)

Driving me nuts

TWCMAD

[nemonoman]

How are you saving the date? as d m (02 06 == june 6?)

SELECT *
FROM mytable
WHERE mydate >= DATE_FORMAT(CURRENT_DATE(),'%d %m')

[twcmad]

Ok have tried a variity of the suggested and am still pulling hair out.

This is the current code,

$dte = gmdate("d/m/y");


SELECT * FROM internal WHERE PosClDate < $dte

PosClDate has the format eg 12/05/04

This works in this testing script

$date2=gmdate("d/m/y"); 
$date1 = "09/07/04";
echo $date1;
echo $date2;
if($date2 >= $date1)
{
echo "closing date is ahead of today".$date1;
}
else
{  echo "Today is greater than closing date";}

What am I doing wrong ?

[LordShryku]

What field type are you storing that date in? Normal mysql date fields are Y-m-d, which leads me to believe you're probably using a non-date type field which couldn't handle comparisons.

[twcmad]

I have been struggling with dates and mysql to tell you the truth. What I have eneded up doing is creating a var from date() usually date("d/m/y")

and putting htis into the database.

I am guessing by your comments that this is not a good way of doing it.

could you give me some guidence on this. On fine on most mysql and php stuff but for some reason dates shake me.

Thanks

[nemonoman]

What is PosClDate supposed to be? How do you create
the value you insert?

As you've set out the SQL, you're comparing strings, not dates.

This comparison MIGHT work if you compared yyyy/mm/dd type dates. But dd/mm/yy comparison will not work

(Also in any case you'd need to quote '$dte' : SELECT * FROM internal WHERE PosClDate < '$dte')

You are so hosed using the approach you've taken, I scarcely know where to start...

What version of MySQL are you using? If 4.1.1 or later, you might have a prayer.

[LordShryku]

The latest version still doesn't support casting to date from a string, so like he said, you're pretty hosed. You're going to have to do a lot of clean up work to fix your database before this will work. The best way would be to create a new date column, with the date column type. Then you'll need to write a script to go through and pull those dates, convert them to mysql readable dates, and insert them back in. After that, you can drop the old column. You could just write the script to convert the dates, and use php to do your comparison, but your best bet would be to fix this now before it blows up in your face. Another popular way of storing dates is in a unix timestamp, in a bigint type field. Then it's just a numeric comparison.

Good luck....