Jump to content


Photo

How to format a date into MySQL [RESOLVED]


  • Please log in to reply
4 replies to this topic

#1 AdRock

AdRock
  • Members
  • PipPipPip
  • Advanced Member
  • 911 posts

Posted 23 August 2006 - 01:43 PM

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

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 18 August 2006

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
If your topic has been solved, please mark the topic as SOLVED.

This helps others from identifying which topics need help still

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 23 August 2006 - 01:50 PM

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

that will put it into your "YYYY-MM-DD" format that mySQL requires.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 lessthanthree

lessthanthree
  • Members
  • PipPipPip
  • Advanced Member
  • 85 posts
  • LocationUK

Posted 23 August 2006 - 02:29 PM

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

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

call me a safe bet, i'm betting i'm not

#4 AdRock

AdRock
  • Members
  • PipPipPip
  • Advanced Member
  • 911 posts

Posted 24 August 2006 - 02:40 PM

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

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

Here is the code that inserts/updates the record
$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);

If your topic has been solved, please mark the topic as SOLVED.

This helps others from identifying which topics need help still

#5 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 24 August 2006 - 03:49 PM

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

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

Here is the code that inserts/updates the record

$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);


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:
<?php
$event = stripslashes($_POST['txtEvent']);
list($day, $month, $year) = explode("-", $event);
$EventDate = date('Y-m-d', strtotime("$month/$day/$year"));
?>

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
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users