Strange PHP MySql Date problem?!

amycrystal123

Hi guys,
I have come with another problem..

I am using MySql database and coding in PHP. I have a "members" table with a field for the member's date of birth. I validate the date and store them to my table in the mysql format (yyyy-mm-dd).

But, the dates are all stored wrong. Most of the time, it is stored as 0000-00-00 or just makes up it's own date.
For example: For a date like 2002-12-12, it stores as 0000-12-00 or 2000-00-12.. (The way it stores is infinite possibilities.. everytime in a new way!)

Why does that happen? I cannot rely on the dates I retrieve if it happens like this.. Where should I check? PLEASE HELP!!!!!!!!!!!!!!

Thanks.
Amy.

fizzwizz

Dont define the field as a date .. define it as char I always store dates as characters!

rgermain

Storing them as characters isn't a really good idea. If you ever need to search between 2 dates and they cross years it messes the whole system up. Try storing the date as a Timestamp. Before changing anything.... What does the string look like before you enter it into the database?

parawizard

they have to be a different format to enter the DATE field i believe. Yet its not hard to grab things that are between dates! you start the output loop at the start date and u end the loop if u pull out the max date 😉

amycrystal123

Hi guys,
Thanks for your suggestions.. Actually, I am on right tracj wrt dates.
I made a mistake in the way I stored them to my DB. I stored them as:
insert into DATE_TABLE values My_Date = $aDate;

The right method is:
insert into DATE_TABLE values My_Date = "'".$aDate."'";

Now - They are stored correctly..!

I have one more doubt.. How do I check if the date is lesser or equal to today's date:
I had a code like this: ($aDate is stored in this format - 12/02/2002 - mm/dd/yyyy format)

if ($aDate <= date('m/d/Y'))
echo "Correct date";
else
echo "Incorrect date";

Even if I give the $aDate value lesser or equal to the current date, I get incorrect date. Why?

yelvington

If you use MySQL's native timestamp type, and the MANY well-documented SQL functions for manipulating and comparing dates, you will NOT have these silly problems.

It is VERY bad advice to stick dates willy-nilly into the database into other column types.

A tutorial on date calculations:
http://www.mysql.com/doc/en/Date_calculations.html

Date and time types defined:
http://www.mysql.com/doc/en/Date_and_time_types.html

Date and time functions:
http://www.mysql.com/doc/en/Date_and_time_functions.html

nfzgrld

The trick to using dates with php/mysql is the understand first that the YYYY-MM-DD layout is the only one that will go into MySQL. NO matter what you put in there, that's what you'll get. The trick is to know what to do with it when you pull it out. Go to php.net and look up the date() and strtotime() functions. With those two you can do just about anything with time and date information.

Nick