Jump to content

Archived

This topic is now archived and is closed to further replies.

AdRock

How to format a date into MySQL [RESOLVED]

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
...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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.