RE: [PHP-DB] figuring out number of months between two dates
select PERIOD_DIFF(DATE_FORMAT("2002-04-01","%Y%m"), DATE_FORMAT("2001-0801","%Y%m") ) as dd; +--+ | dd | +--+ |8 | +--+ 1 row in set (0.00 sec) -Original Message- From: John Hughes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 02, 2002 2:28 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] figuring out number of months between two dates I'm trying to get PERIOD_DIFF to work in my quest to count months between dates. I've run into a stumbling block: LEFT(CURRENT_DATE,7) produces 2002-04, as you would expect. LEFT(week_date,7) WHERE week_no = '1' produces 2001-08, again as expected (see table below) But PERIOD_DIFF(LEFT(CURRENT_DATE,7) ,LEFT(week_date,7) ) WHERE week_no = '1' produces 1. The trouble appears to be the '-' in the dates produced by LEFT. When I just put PERIOD_DIFF(200204,200108) the result is 8, which is the correct answer. So, how do I produce the year and month digits without the intervening hyphen so I can feed the data to PERIOD_DIFF? - Original Message - From: "John Hughes" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, April 01, 2002 11:56 PM Subject: [PHP-DB] figuring out number of months between two dates > I have a table that contains a list of dates. For instance: > > 2001-08-23 > 2001-08-27 > 2001-09-04 > (and running through) > 2002-06-03 > > I want to calculate how many months between 2001-08-23 and CURRENT_DATE > (which for discussion purposes we'll put at 2002-04-01) and how many months > between the first week and the last. > > I can count the weeks and divide by 4.333 to approximate months, but I was > wondering if there was a way to use a PHP function or MySQL's MONTH() to do > the job. > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] figuring out number of months between two dates
Does this help? select 12*(year(current_date)-year('2001-03-02'))+(month(current_date)-month('2001- 03-15')); the result is 13. But it's quite a large query. - Original Message - From: "John Hughes" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, April 02, 2002 9:56 AM Subject: [PHP-DB] figuring out number of months between two dates > I have a table that contains a list of dates. For instance: > > 2001-08-23 > 2001-08-27 > 2001-09-04 > (and running through) > 2002-06-03 > > I want to calculate how many months between 2001-08-23 and CURRENT_DATE > (which for discussion purposes we'll put at 2002-04-01) and how many months > between the first week and the last. > > I can count the weeks and divide by 4.333 to approximate months, but I was > wondering if there was a way to use a PHP function or MySQL's MONTH() to do > the job. > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] figuring out number of months between two dates
I'm trying to get PERIOD_DIFF to work in my quest to count months between dates. I've run into a stumbling block: LEFT(CURRENT_DATE,7) produces 2002-04, as you would expect. LEFT(week_date,7) WHERE week_no = '1' produces 2001-08, again as expected (see table below) But PERIOD_DIFF(LEFT(CURRENT_DATE,7) ,LEFT(week_date,7) ) WHERE week_no = '1' produces 1. The trouble appears to be the '-' in the dates produced by LEFT. When I just put PERIOD_DIFF(200204,200108) the result is 8, which is the correct answer. So, how do I produce the year and month digits without the intervening hyphen so I can feed the data to PERIOD_DIFF? - Original Message - From: "John Hughes" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, April 01, 2002 11:56 PM Subject: [PHP-DB] figuring out number of months between two dates > I have a table that contains a list of dates. For instance: > > 2001-08-23 > 2001-08-27 > 2001-09-04 > (and running through) > 2002-06-03 > > I want to calculate how many months between 2001-08-23 and CURRENT_DATE > (which for discussion purposes we'll put at 2002-04-01) and how many months > between the first week and the last. > > I can count the weeks and divide by 4.333 to approximate months, but I was > wondering if there was a way to use a PHP function or MySQL's MONTH() to do > the job. > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php