Date format

[scarlson]

I am needing a way to display my DATES in a MMDDYY format.  I am using mySQL for the database and i am storing the info in a DATE type. 

 

I know there is a DATE_FORMAT() but not sure on how to use it or where to use it at.  Here is the code for my date field box:

 

<label>Start Date
              <input type="text" name="start_date" id="start_date" value="<?php echo $garage_sale_start_date ?>"/>
              </label>

 

Is the DATE_FORMAT() used here or at the query?

[zq29]

DATE_FORMAT() is a MySQL function.

[scarlson]

So I have to use this in my query for the date?  I will be using PHP to do the query, not sure how to use DATE_FORMATE().  Anyone have an example to look at with PHP?

[ikmyer]

depending on what date you are storing in the Database... you should be able to use the following to reorder the date as you want...

 

<?php

$old_date = strtotime($garage_sale_start_date);
$new_date = date("F j, Y", $old_date);         // using the php function date() to create new format
echo $new_date;

?>

 

http://us3.php.net/manual/en/function.date.php

[scarlson]

Ok, that works.  My next question is, is that the date is coming back as December 7, 2007.  What can I do so it's just:  12-07-2007?

 

Also, if the user wants to change this date at some point, do I have to reconvert the date back for mySQL?  If so, how?

 

Scott

[revraz]

Look up the date() function at php.net and you will see how to display it differently.

 

As for changing it back, its just as easy to use date() again and put it back the same way.  But you may want to add validation to make sure it's in the right format if the user changes it, because what if they enter yr/mo/dd or mo/dd/yr, or some other unexpected format.

[ikmyer]

change the "F j, Y" to change the format...

 

again... the formatting options can be found here....

http://us3.php.net/manual/en/function.date.php

 

[ikmyer]

if they want to change the date... just update the database with the new date, in the same format as the rest.

 

No need to "convert" back... the date is still the same in the database...

[scarlson]

Well when ever I try to update the date, it erases the data in the database and when i log back in the form should pre-load the data back and I always get this date:  30-11-1999

 

If I manually input the date into the database and then login, I get the correct results:

 

$start_date = $_POST['start_date'];

 

mysql_query("UPDATE garage_sale SET start_date = '$start_date' WHERE login_id = '$logid' ") or die(mysql_error());

 

Any ideas?

[revraz]

echo $start_date before your Update so you can see what is actually trying to be entered.

[ikmyer]

my guess would be mysql doesn't like the format your sending to it...

[Yesideez]

When I use PHPs date() function I refer to this page for help with formatting it:

http://us2.php.net/date

 

I always store dates in my MySQL databases as unsigned integers using PHPs time() function to get the current date and time. When I get this back I only have to use date() to format it:

<?php
  $fetch=mysql_fetch_assoc(mysql_query("SELECT mydate FROM mytable"));
  echo date("d-M-Y H:i:s",$fetch['mydate']);
?>

 

Some prefer to store dates & times in databases using MySQLs own datatypes (to let MySQL do most of the work) but not being that savvy with MySQL, I find the above method easiest.

[revraz]

Its also easier to manipulate dates in unix timestamp than in mysql date.