Edit Question - KnowledgeHub

Title Body

I am trying to get the date and time duration between the Loan taken and Paid date. I used the PHP date and time functions, but it is not always accurate. How can I do this accurately in MySQL? Let assume two dates, The Loan taken date 2009-05-24 and the Loan return date 2012-04-30 I write a MySQL query SELECT DATEDIFF('2012-04-30', '2009-05-24') `total_days`; return 1072 days, which is roughly 2 Years, 11 Months, 12 Days. Please do not answer with PHP code, I already try it. Here is the code. The function below uses PHP >= 5.3 functions and convert days to years, months and days. function date_interval($date1, $date2) { $date1 = new DateTime($date1); $date2 = new DateTime($date2); $interval = date_diff($date2, $date1); return ((($y = $interval->format('%y')) > 0) ? $y . ' Year' . ($y > 1 ? 's' : '') . ', ' : '') . ((($m = $interval->format('%m')) > 0) ? $m . ' Month' . ($m > 1 ? 's' : '') . ', ' : '') . ((($d = $interval->format('%d')) > 0) ? $d . ' Day' . ($d > 1 ? 's' : '') : ''); } The function below uses PHP >= 5.2 functions and convert days to years, months and days. function date_interval($date1, $date2) { $diff = abs(strtotime($date2) - strtotime($date1)); $years = floor($diff / (365 * 60 * 60 * 24)); $months = floor(($diff - $years * 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24)); $days = floor(($diff - $years * 365 * 60 * 60 * 24 - $months * 30 * 60 * 60 * 24) / (60 * 60 * 24)); return (($years > 0) ? $years . ' Year' . ($years > 1 ? 's' : '') . ', ' : '') . (($months > 0) ? $months . ' Month' . ($months > 1 ? 's' : '') . ', ' : '') . (($days > 0) ? $days . ' Day' . ($days > 1 ? 's' : '') : ''); }

Tags (comma-separated) Cancel