Extra month or day in MySQL date field in PHP

Question

I have a problem where I need to handle dates when parts of the month and day are optional. For example, the year will always be known, but sometimes the day or month and day will be unknown.

In MySQL, I can create a table with a date field, and although I cannot find the link in the MySQL manual, it will take the following values:

(YYYY-MM-DD format):
2011-02-10    // Current date
2011-02-00    // Day unknown so replaced with 00
2011-00-00    // Day and month unkown so replaced with 00-00

Test calculations from the database work fine, so I can easily sort the results. The manual says that the month should be between 01 and 12 and the day between 01 and 31, but it takes 00.

First question: will I have difficulty using 00 in months or days, or is this acceptable?

The next question is: is there a PHP function (or a MySQL format command) that automatically formats the following dates to the desired format string?

2011      becomes   2011-00-00
2011-02   becomes   2011-02-00

Or do I need to write a special function for this?

The following does not work:

<?php
$date = date_create_from_format('Y-m-d', '2011-00-00');
echo date_format($date, 'Y-m-d');
// Returns 2010-11-30

$date = date_create_from_format('Y-m-d', '2011-02-00');
echo date_format($date, 'Y-m-d');
// Returns 2011-01-31 
?>

Third question: is there a PHP function (or a MySQL command) to format dates for use in PHP?

Finally, is this a better approach? Or is there a "best practice" method?

---

EDIT:

Here is what I am doing now:

The date field can accept a date in the format YYYY, YYYY-MM or YYYY-MM-DD and is processed in this function before sending to the database:

/**
* Takes a date string in the form:
*   YYYY or
*   YYYY-MM or
*   YYYY-MM-DD
* and validates it
* 
* Use date_format($date, $format); to reverse.
* 
* @param string $phpDate Date format [YYYY | YYYY-MM | YYYY-MM-DD]
* 
* @return array 'date' as YYYY-MM-DD, 'format' as ['Y' | 'Y-m' | 'Y-m-d'] or returns false if invalid
*/
function date_php2mysql($phpDate) {
    $dateArr = false;
    // Pattern match
    if (preg_match('%^(?P<year>\d{4})[- _/.]?(?P<month>\d{0,2})[- _/.]?(?P<day>\d{0,2})%im', trim($phpDate), $parts)) {
        if (empty($parts['month'])) {
            // Only year valid
            $date = $parts['year']."-01-01";
            $format = "Y";
        } elseif (empty($parts['day'])) {
            // Year and month valid
            $date = $parts['year']."-".$parts['month']."-01";
            $format = "Y-m";
        } else {
            // Year month and day valid
            $date = $parts['year']."-".$parts['month']."-".$parts['day'];
            $format = "Y-m-d";
        }
        // Double check that it is a valid date
        if (strtotime($date)) {
            // Valid date and format
            $dateArr = array('date' => $date, 'format' => $format);
        }
    } else {
        // Didn't match
        // Maybe it is still a valid date
        if (($timestamp = strtotime($phpDate)) !== false) {
            $dateArr = array('date' => date('Y-m-d', $timestamp), 'format' => "Y-m-d");
        }
    }
    // Return result
    return $dateArr;
}

Thus, the pattern matches the input of $ phpDate, where it must begin with 4 digits, and then optionally pairs of digits for the month and day. They are stored in an array called $ parts.

Then it checks if months or days exist by specifying a format string and creating a date.

, , , . FALSE.

, , .

- ?

Answer 1

, , . , , .

" " , 2011-04-01 (), 2011-04 (= 2011) 2011 (year- ) . , MySQL "2011-00-00", , .
  MySQL database ODBC, , "" (Ex: "2011-04-00" ACCESS, MySQL-ODBC.
, MySQL VARCHAR(10): , MySQL, , , , php date date_php2mysql().

, , MySQL SQL-, MySQL . ( !)

. , MySQL VARCHAR(10) aaaa-mm-jj . .

Answer 2

, , ? VARCHAR? 2011-02-00 , , mysql PHP . str_to_time , , MySQL? , 1 31 , 00, , .

2011-02-00 , PHP . , , 2001-01-31, . , , . , .

Answer 3

. PEAR Date. , PEAR Date. , , Date.

Answer 4

. , , .

http://books.google.co.uk/books?id=s_87mv-Eo4AC&pg=PA145&lpg=PA145&dq=mysql+date+of+death+when+month+unknown&source=bl&ots=tcRGz3UDtg&sig=YkwpkAlDtBP1KKTDtqSyZCl63hs&hl=en&ei=Btf5TbL1NIexhAfkveyTAw&sa=X&oi= book_result & ct = result & resnum = 8 & ved = 0CFMQ6AEwBw # v = onepage & q & f = false

Answer 5

If you pull the date from a piece from the database, you can get it as if it had 3 fields.

YEAR(dateField) as Year, MONTH(dateField) as Month, DAY(dateField) as DAY

Then clicking these fields in the appropriate fields in the next bit of PHP will give you the result you are looking for.

$day = 0;
$month = 0;
$year = 2013;


echo $datestring;

$format = "Y";
if($month)
{
    $format .= "-m";
    if($day)
        $format .="-d";
    else
        $day = 1;
}
else
{
    $month = 1;
    $day = 1;
}

$datestring = strval($year)."-".strval($month)."-".strval($day);

$date = date($format, strtotime($datestring));
echo $date; // "2013", if $month = 1, "2013-01", if $day and $month = 1, "2013-01-01"