Page 1 of 1

PHP MyAdmin

Posted: Tue Jun 26, 2007 4:30 am
by aceconcepts
Hi,

I use PHP MyAdmin to administer my MySQL databases.

In some of my tables I have a date field and I have the data type set as DATE. When I insert a date value from a web page into one of these date fields, it does not display. A default mask is used i.e. 00/00/0000.

The date value I insert is formatted as: date("d/m/y")

I know its quite difficult to comment on because you dont know my setup but any general suggestions would be greatly appreciated.

Thanks.

Posted: Tue Jun 26, 2007 4:35 am
by volka
try something like '2007-06-26', i.e. yyyy-mm-dd as a string

Posted: Tue Jun 26, 2007 4:46 am
by aceconcepts
Hi,

I have tried recording the date as a string, however, when it comes to sorting the dates i.e. ORDER BY field_date the dates do not get sorted correctly.

e.g. this is how the dates get sorted when defined as strings

01/05/2007
03/01/2007
10/05/2007...

it sorts them from the start of the string.

Posted: Tue Jun 26, 2007 5:15 am
by volka
yyyy-mm-dd but mm/dd/yyyy
It's sorting them correctly.

Posted: Tue Jun 26, 2007 5:18 am
by aceconcepts
So i should be storing them like: yyyy-mm-dd not: dd-mm-yyyy?

Posted: Tue Jun 26, 2007 5:22 am
by volka
mysql has to perform a type cast string->date. and it can only understand that much formats, You have to choose a valid format for the input.
Take a look at http://dev.mysql.com/doc/refman/5.1/en/datetime.html
There's a section starting with
You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

Posted: Mon Jul 02, 2007 11:21 am
by gurjit
when you add the date to the database add like

Code: Select all

<?php
// todays date
$today = date("Y-m-d");
?>
When you out put the date from the database use:

Code: Select all

<?php
// $date_field is the field name in the database
echo date("d M Y", strtotime($date_field)); 

?>
The database stores date in the format Y-m-d. If you are storing time and date, then store as:

Code: Select all

<?php
// todays date
// G is hour, i is minute, s is seconds 
$today = date("Y-m-d G:i:s");
?>
Read about time and date function here:
http://uk3.php.net/manual/en/function.date.php

Posted: Mon Jul 02, 2007 4:01 pm
by RobertGonzalez
A date filed in MySQL stores dates as YYYY-MM-DD by default. A DateTime field stores them as YYYY-MM-DD HH:MM:SS by default. When you insert dates into that database, it is best to insert them in the format that it is expecting otherwise it may have to guess. In which case it may guess wrong and you may get a 0000-00-00 date.