Uploading wrong date format

PHP
1

Hi all,

I have a form and as part of it the date its created is uploaded too.

With that data in the database an excel file is generated and the data has to be in a certain way for their software to accept it, and one of those is the date created bit.

So in my code I am doing this:


$current_time = date ('Y-m-d H:i:s');

And its being uploaded to the database with the query below:


$sql = mysql_query("INSERT INTO hazzard (Name, Employerofperson, Contactphone, Location, DateOccured, ProductionZone, NearMissDetails, Anyimmediateactions) VALUES ('".mysql_real_escape_string($_SESSION['name_A'])."','".mysql_real_escape_string($_SESSION['companies_A'])."','".mysql_real_escape_string($_SESSION['numbers_A'])."','".mysql_real_escape_string($location)."','$current_time','$ProductionZone','".mysql_real_escape_string($detail)."','".mysql_real_escape_string($feedback)."')") or die (mysql_error());

Which gives me an output like this: 2013-08-29 16:13:31

I need to keep that as it goes, so I need to create another date created filed but htis time so that it outputs like this: 29/08/2013

With creating another field in the database to accomodate this, do I set it a certain way and how also do I create the date that way ready to go in the update script.

2
  1. There is no reason to store the same value twice in a db, even if its formatted differently. You can format it how you need to when you use it within a query.
  2. You can create a column in the db with default value of the current date and time, this way you dont even have to worry about inserting the date value.
3

So create the column and make it create automatically is it.

4

With the current column date field showing as :2013-08-29 18:20:42

How would I re-format that on the page to output as 29-August-13

Thats basically what I need, rather than like you say adding another column in.

5

You can format the date as part of your SELECT query when you’re fetching the data for output:

SELECT DATE_FORMAT(DateOccured, '%e-%M-%y') as my_date FROM hazzard
6

Hi fretburner,

That was exactly what I needed, and it worked perfectly.

Cheers

7

Hi fretburner,

Just in case the request is made over the weekend, how can your script be adapted to also include the time, just added onto the end of what you suggested.

Cheers

8

You’d just need to add extra formatting codes… you can find a list of them here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format - it’s possible to output the date/time in just about any format you could need. :slight_smile: