How to format a date into MySQL [RESOLVED]

[AdRock]

I have a text field on a form with a date that is entered using this format [code]8/16/2006[/code]

I would like to insert it as a date in a MySQL database and when i want to display it from the database I would like it in this format [b]18 August 2006[/b]

How would I go about inserting it into the database into a recgonisable format.  I can display it in the required format once it's in the database but i need to get it in there first

[obsidian]

try something like this:
[code]
<?php
$myDate = "8/16/2006";
$myInsertDate = date('Y-m-d', strtotime($myDate)); // insert this one
?>
[/code]

that will put it into your "YYYY-MM-DD" format that mySQL requires.

[lessthanthree]

...and to display it how you want when you take it back out of the database:

[code]
$myDate = "2006-12-10"; //this will actually come from your mysql table
print date("d F Y", strtotime($myDate));
[/code]

[AdRock]

I have a really strange problem with changing the date into a sql date

I selected a date of [b]30-10-2006 [/b] and in the database it inserted the date as [b]2036-01-27[/b]

Here is the code that inserts/updates the record
[code]$event = stripslashes($_POST['txtEvent']);
...
....
....
    $EventDate = date('Y-m-d', strtotime($event));

    $query="UPDATE events SET title='$name', eventdate='$EventDate', content='$message', photo='$pic' WHERE id='$ud_id'";
    mysql_query($query);[/code]

[obsidian]

[quote author=AdRock link=topic=105335.msg421399#msg421399 date=1156430413]
I have a really strange problem with changing the date into a sql date

I selected a date of [b]30-10-2006 [/b] and in the database it inserted the date as [b]2036-01-27[/b]

Here is the code that inserts/updates the record
[code]$event = stripslashes($_POST['txtEvent']);
...
....
....
    $EventDate = date('Y-m-d', strtotime($event));

    $query="UPDATE events SET title='$name', eventdate='$EventDate', content='$message', photo='$pic' WHERE id='$ud_id'";
    mysql_query($query);[/code]

[/quote]

that's because 30-10-2006 is not a typical readable date format for strtotime(). when you're working with dates and times, it's usually a MM/DD/YYYY or YYYY-MM-DD format. so, in your case, when it's a DD-MM-YYYY, you'll need to do something like this:
[code]
<?php
$event = stripslashes($_POST['txtEvent']);
list($day, $month, $year) = explode("-", $event);
$EventDate = date('Y-m-d', strtotime("$month/$day/$year"));
?>
[/code]

the only reason i pass the variables through the date() and strtotime() functions again is to be absolutely certain that the value entered into the database is the right format.

hope this helps