HELP! Date/Time--I'm completely lost.

I know there's a million articles out there, but I've read about 20 and I keep getting more confused. I have 2 DATE/TIME fields in a MySQL database for employees.

I'm just trying to do a simple update form--everything works fine, but I"m getting all sorts of values (not the correct ones) for the dates. Somehow it doesn't seem to be getting into MySQL correctly.

So I'll have a form field, DOB, where I enter in 4/16/74. I just use a standard INSERT, which puts it into mysql....INSERT blah blah blah DOB VALUES blah blah blah '4/16/74'

Great. But I can't seem to get that 4/16/74 into and out of the database.

I've tried playing with strtotime and date (m-d-Y), which gets closer, but I"m going out of my mind on this....

$DOB=strtotime($DOB);
$DOB=date('Y-d-m',$DOB);

but still no luck....

any thoughts on things I might be missing?

Thanks!

Steve

MySQL date types take the date in the format of YYYY-MM-DD. You need to convert the date entered into your form into that format before it will work correctly.

Your date() call isn't correct -- the format string should be Y-m-d. Also, if your date is in European format instead of US standard, strtotime() will not process it correctly.

If you only want it to display like 4/16/74. I would store it in MySQL in standard form 0000-00-00
when you pull it out of the database as a variable ie., $dob

use this:
fdate($dob);

function fdate($fdob) {
list($year,$month,$day) = split('[-]', $fdob);
print "$month/$day/$year";
}


QrycheOM

Just use MySQL's DATE_FORMAT() function to set your display format.

Thanks for all the help--got the output working fine, as long as I enter in the dates in the standard format.

BUT, for user convenience, what if they want to enter in a normal date format, say 4/6/05 or April 5, 2005? I thought that MySQL was able to recognize that kind of entry and convert it accordingly?

If not, is there an easy way to convert a user-entered date into the right format?

Thanks!

Steve

MySQL date types only recognize dates in year-month-day format. You can use MySQL's STR_TO_DATE() function to convert, but you have to know how the user's entered the date in advance.

To convert in your script, I'd recommend using the strtotime() and date() functions to get a proper format. strtotime() can accept a number of different formats (a link to them is available from the manual page).

Safest, though, is to limit the user's options (especially if you're going to have international users). I wouldn't allow them to just enter whatever they want into a text-box. I'd force them to either use an easily-converted but user-friendly m/d/y set up, or I'd provide a series of dropdowns that can be put into a workable string upon submission.

Thanks, folks--

Turns out I was using the strtotime and date functions correctly, but I converted them for display in the form fields, rather than before inserting them into MySQL. So I just moved everything up in the code and all's peachy--