Hi all,

I am using the following code to convert 1963-11-10 from mysql to 10/11/1963

$dob = $datarow['dateofbirth'];
list($year, $month, $day) = explode('-', $dob);
$usedob = date("d/m/Y", mktime(0, 0, 0, $month, $day, $year));

But when it is viewed on screen, it displays the date as 31/12/1969.

I am also displaying many other dates of births using this code on the same page which are displaying perfectly fine.

Any suggestions why or how to fix?

Quick response would be great!

Regards
K

    May it be due to this?
    "Before PHP 5.1.0, negative timestamps were not supported under any known version of Windows and some other systems as well. Therefore the range of valid years was limited to 1970 through 2038." (http://www.php.net/manual/en/function.mktime.php)

    Anyway, we don't you do the formatting right in your sql query, like
    DATE_FORMAT(colname, '%d/%m/%Y')
    (see the list in the the DATE_FORMAT section for formatting options - http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html)

      11 days later

      Hi xblue,

      I have used the function you recommended:

      DATE_FORMAT(dateofbirth, '%d/%m/%Y')

      But this simply changes the data from the dateofbrth field to todays date.

      I have read the function descriptions on mysql.com but to no avail. Have you any suggestions?

      Kind regards
      K

        Yes, this problem is caused by the inability of PHP prior to 5.1 to handle negative timestamps, all negative timestamps are returned as -1 so 1 second off of Jan1st 1970 is Dec 31st 1969, the date you are getting.

        Might be worth trying
        DATE_FORMAT(dateofbirth, '%d/%m/%Y') AS newdateofbirth
        and seeing what is in the array index ['newdateofbirth']

        Blu

          Here's some partial code for creating a negative timestamp...

          if ($year<1970)
  {
  //First, calculate the total complete years worth of days...
  $year_diff=(1970-$year)-1; //total years away...
  $Full_Years_Days=(floor($year_diff/4)*365+1)+($year_diff%4*365);
  if ($year_diff%4==2 || $year_diff%4==3) $Full_Years_Days=$Full_Years_Days+1;

  //Next determine partial year's days difference  
          
  switch ($month)
    {
    case "January":
    //we count days from February on initially...
    if ($year%400==0 || ($year%4==0 && $year%100!=0))
      $Full_Month_Days=335;
    else $Full_Month_Days=334;
    //then we subtract the days from the end of the month 
    $extra_days=31-$day+1;
    $Full_Month_Days=$Full_Month_Days+$extra_days;
    break;

case "February":
if ($year%400==0 || ($year%4==0 && $year%100!=0))
  $end_day=29;
else $end_day=28;
$Full_Month_Days=305;
$Full_Month_Days=$Full_Month_Days+($end_day-$day+1);
break;

case "March":
$Full_Month_Days=274;
$Full_Month_Days=$Full_Month_Days+(31-$day+1);
break;

case "December":
$Full_Month_Days=30-$day+1;
break;
}

  //Now we add all the days together, multiply by seconds, and turn it negative.
  $timestamp=($Full_Years_Days+$Full_Month_Days)*86400*(-1);
  }

          I did the tough stuff, you can figure out the rest of the months. If you want, you could create a TimeStamp() function to figure out what the correct timestamp was, based on whether it was above or below January 1st, 1970. What you do with it beyond this is up to you...

            You can do this without use of mktime() function:

             
 $dob = $datarow['dateofbirth'];
 list($year, $month, $day) = explode('-', $dob); 
 $usedob = $day.'/'.$month.'/'.$year;

              $mysqldate ='1963-11-10';
$mydate = date("m-d-Y",strtotime($mysqldate));
echo "Mysql date is now $mydate!";//Prints11-10-1963

              Source : PHP Manual strtotime()

              Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer.) Additionally, not all platforms support negative timestamps, therefore your date range may be limited to no earlier than the Unix epoch. This means that e.g. dates prior to Jan 1, 1970 will not work on Windows, some Linux distributions, and a few other operating systems. PHP 5.1.0 and newer versions overcome this limitation though.

                Houdini - your code isn't exactly version/OS friendly, specifically to the Windows platform (RE: negative timestamps mentioned above).

                As mentioned above, this query:

                SELECT DATE_FORMAT(`dateofbirth`, '%d/%m/%Y') as `dateofbirth` FROM `test`;

                returns this result set:

                dateofbirth  
                
10/11/1963

                  Some helpful soul posted this to the list recently, to do the job the reverse way, and this works with PHP 4 running on Windows. I've changed round the way it works to do the job requested.

                  $date="1963-11-10";

list($year,$month,$day) = split('[-./]', $date);
     $val=$day."/".$month."/".$year;

echo "Date is ".$val;
echo "<br>";

                  Blu

                    But I still don't get why we're wasting PHP's time with trying to manipulate a date when MySQL is fully capable of doing the task??

                    Why give PHP the wrong format and make it deal with it instead of just starting with the right format in MySQL using DATE_FORMAT() ?

                      Write a Reply...