I have this small sample from a database:

id_doc	fecIn	fecAlt
04564494	2019-12-22	2020-01-22
04564498	2019-12-22	2020-02-06
04512870	2020-01-03	2020-01-05
04566760	2020-01-07	2020-02-12
04500207	2020-02-29	2020-03-20
04502614	2020-03-19	2020-05-27
04503127	2020-03-31	2020-06-02

I want to count months between 'fecIn' and 'fecAlt', without taking into account the days in the dates.

So i Try:

SELECT 
d.id_doc,
d.fecIn,
d.fecAlt,
DATE_FORMAT(d.fecIn,'%Y-%m') as MonthIn,
DATE_FORMAT(d.fecAlt,'%Y-%m') as MonthAlt
FROM test.docs d

So far I get only year and month. I stuck here . Is there a way to count months from the way I get 'MonthIn' and 'MonthAlt'? The Result I want is the last column:

id_doc	fecIn	fecAlt	MontIn	MonthAlt	Result Expected
04564494	2019-12-22	2020-01-22	2019-12	2020-01	2
04564498	2019-12-22	2020-02-06	2019-12	2020-02	3
04512870	2020-01-03	2020-01-05	2020-01	2020-01	1
04566760	2020-01-07	2020-02-12	2020-01	2020-02	2
04500207	2020-02-29	2020-03-20	2020-02	2020-03	2
04502614	2020-03-19	2020-05-27	2020-03	2020-05	3
04503127	2020-03-31	2020-06-02	2020-03	2020-06	4

I tryed with:

TIMESTAMPDIFF

but no with the result that I want. Some help please.

CodePudding user response：

To find the difference in months between two dates, you can use the TIMESTAMPDIFF function in MySQL. The TIMESTAMPDIFF function takes three arguments: the unit to use for the difference (in this case MONTH), the starting date, and the ending date. Here is an example of how you could use it in your query:

SELECT 
    d.id_doc,
    d.fecIn,
    d.fecAlt,
    DATE_FORMAT(d.fecIn,'%Y-%m') as MonthIn,
    DATE_FORMAT(d.fecAlt,'%Y-%m') as MonthAlt,
    TIMESTAMPDIFF(MONTH, d.fecIn, d.fecAlt) as months_difference
FROM test.docs d

This will return the difference in months between fecIn and fecAlt in the months_difference column. The value in this column will be an integer, and it will not include any days or other units of time. For example, if fecIn is 2019-12-22 and fecAlt is 2020-01-22, the months_difference value will be 1, because there is one month between the two dates.

CodePudding user response：

I follow the link from:

dev.mysql.com/doc/refman/8.0/en/… – P.Salmon

So try with PERIOD_DIFF and a plus 1. with the result that I wanted.

SELECT *,
PERIOD_DIFF(a.MonthAlt,a.MonthIn) 1 as CountMonth
FROM(
SELECT 
d.id_doc,
d.fecIn,
d.fecAlt,
DATE_FORMAT(d.fecIn,'%Y%m') as MonthIn,
DATE_FORMAT(d.fecAlt,'%Y%m') as MonthAlt
FROM test.docs d
) as a

If there is a better way to do this. please let me know. Thank you!, I appreciate your help for all of you. I learned more things from your answers.

CodePudding user response：

You can use the TIMESTAMPDIFF function to count the number of months between two dates. The TIMESTAMPDIFF function takes three arguments: the unit to use for the result (in this case, MONTH), the start date, and the end date.

Here's an example of how you can use the TIMESTAMPDIFF function to get the number of months between two dates:

SELECT 
  d.id_doc,
  d.fecIn,
  d.fecAlt,
  TIMESTAMPDIFF(MONTH, d.fecIn, d.fecAlt) AS months_diff
FROM test.docs d

This will give you the number of months between the fecIn and fecAlt columns in the docs table. The result will be stored in the months_diff column.

If you want to get only the year and month part of the dates in the fecIn and fecAlt columns, you can use the DATE_FORMAT function, like you did in your original query. Here's an example of how you can combine the DATE_FORMAT and TIMESTAMPDIFF functions:

SELECT 
  d.id_doc,
  d.fecIn,
  d.fecAlt,
  DATE_FORMAT(d.fecIn, '%Y-%m') AS month_in,
  DATE_FORMAT(d.fecAlt, '%Y-%m') AS month_alt,
  TIMESTAMPDIFF(MONTH, d.fecIn, d.fecAlt) AS months_diff
FROM test.docs d

This query will give you the number of months between the fecIn and fecAlt columns, as well as the year and month part of each date, stored in the month_in and month_alt columns.

I hope this helps! Let me know if you have any other questions.