How to show records between two dates?

To do that you can use the BETWEEN condition, I do not know if the base is in MySql, or Sql Server, but it would be something very similar to:

SELECT 
    * 
FROM 
    order_details 
WHERE 
    order_date BETWEEN CAST('2014-02-01' AS DATE) AND CAST('2014-02-28' AS DATE);

Documentation: link

Greetings

The following query could help you:

DECLARE @fecha_adquisicion_fin DATE;

SET @fecha_adquisicion_fin : = '20170301'

SELECT id_adquisicion
    ,MONTH(fecha_adquisicion) AS mes_inicio
    ,MONTH(fecha_adquisicion_fin) AS mes_fin
    ,YEAR(fecha_adquisicion)
FROM TBLADQUISICIÓN
WHERE fecha_adquisicion >= '20150401'
    AND fecha_adquisicion <= @fecha_adquisicion_fin

This goes for long

For this you are going to have to generate a table that contains all the months in the range that you want to consult. The good thing is that you only have to generate it once. For example

CREATE TABLE misfechas AS 
(SELECT DATE(20150101) AS fecha UNION ALL
SELECT DATE(20150201) AS fecha UNION ALL
SELECT DATE(20150301) AS fecha UNION ALL
SELECT DATE(20150401) AS fecha UNION ALL
SELECT DATE(20150501) AS fecha UNION ALL
SELECT DATE(20150601) AS fecha UNION ALL
SELECT DATE(20150701) AS fecha UNION ALL
SELECT DATE(20150801) AS fecha UNION ALL
SELECT DATE(20150901) AS fecha UNION ALL
SELECT DATE(20151001) AS fecha UNION ALL
SELECT DATE(20151101) AS fecha UNION ALL
SELECT DATE(20151201) AS fecha UNION ALL
SELECT DATE(20160101) AS fecha UNION ALL
SELECT DATE(20160201) AS fecha UNION ALL
SELECT DATE(20160301) AS fecha UNION ALL
SELECT DATE(20160401) AS fecha UNION ALL
SELECT DATE(20160501) AS fecha UNION ALL
SELECT DATE(20160601) AS fecha UNION ALL
SELECT DATE(20160701) AS fecha UNION ALL
SELECT DATE(20160801) AS fecha UNION ALL
SELECT DATE(20160901) AS fecha UNION ALL
SELECT DATE(20161001) AS fecha UNION ALL
SELECT DATE(20161101) AS fecha UNION ALL
SELECT DATE(20161201) AS fecha UNION ALL
SELECT DATE(20170101) AS fecha UNION ALL
SELECT DATE(20170201) AS fecha UNION ALL
SELECT DATE(20170301) AS fecha UNION ALL
SELECT DATE(20170401) AS fecha UNION ALL
SELECT DATE(20170501) AS fecha UNION ALL
SELECT DATE(20170601) AS fecha UNION ALL
SELECT DATE(20170701) AS fecha UNION ALL
SELECT DATE(20170801) AS fecha UNION ALL
SELECT DATE(20170901) AS fecha UNION ALL
SELECT DATE(20171001) AS fecha UNION ALL
SELECT DATE(20171101) AS fecha UNION ALL
SELECT DATE(20171201) AS fecha)

the contents of the table would be

fecha
01.01.2015 00:00:00
01.02.2015 00:00:00
01.03.2015 00:00:00
01.04.2015 00:00:00
01.05.2015 00:00:00
01.06.2015 00:00:00
01.07.2015 00:00:00
01.08.2015 00:00:00
01.09.2015 00:00:00
01.10.2015 00:00:00
01.11.2015 00:00:00
01.12.2015 00:00:00
01.01.2016 00:00:00
01.02.2016 00:00:00
01.03.2016 00:00:00
01.04.2016 00:00:00
01.05.2016 00:00:00
01.06.2016 00:00:00
01.07.2016 00:00:00
01.08.2016 00:00:00
01.09.2016 00:00:00
01.10.2016 00:00:00
01.11.2016 00:00:00
01.12.2016 00:00:00
01.01.2017 00:00:00
01.02.2017 00:00:00
01.03.2017 00:00:00
01.04.2017 00:00:00
01.05.2017 00:00:00
01.06.2017 00:00:00
01.07.2017 00:00:00
01.08.2017 00:00:00
01.09.2017 00:00:00
01.10.2017 00:00:00
01.11.2017 00:00:00
01.12.2017 00:00:00

Now you can consult the table between two dates with the format yyyy-mm-dd , and step request the year and month of each date

SELECT YEAR(fecha) agno, MONTH(fecha) mes
FROM misfechas
WHERE fecha>= '2015-12-01' and fecha<='2017-01-01' 

The output of this is:

agno    mes
2015    12
2016    1
2016    2
2016    3
2016    4
2016    5
2016    6
2016    7
2016    8
2016    9
2016    10
2016    11
2016    12
2017    1

And for the same reason you can group with those fields (note that I changed the dates to be more illustrative)

SELECT YEAR(fecha) agno, MIN(MONTH(fecha)) mes_ini, MAX(MONTH(fecha)) mes_fin
FROM misfechas
WHERE fecha>= '2015-11-01' and fecha<='2017-02-01'
GROUP BY YEAR(fecha)

The output is

agno    mes_ini mes_fin
2015    11  12
2016    1   12
2017    1   2

This you have to wrap it in a big parenthesis and cross it with your acquisitions table. What is clear is that the table that contains the entire range of dates you will have to do it yes or yes, because MySQL does not have the type of row generators that have Oracle or PostgreSQL. And if you want to do your queries with a level of precision of days instead of months, you will have to generate every day row by row (you can help with an excel).

There is another way to do it, using a dummy table that has at least as many rows as there are days in your interval, and a variable that is incremented one day at a time, or one month at a time. But generating the dummy table costs you the same effort as generating the table of dates, so I opted to give you the solution without variables or stored procedures.

I have attached an SP with my solution, I hope it will help you

BEGIN
DECLARE Fda, xAno, xCount INT DEFAULT 0;
DECLARE xLimI, xLimS, xId INTEGER;
DECLARE xFec date;

/ *   without parameter control, p From and pHasta you should check them * /

DECLARE cur_reng cursor FOR select     distinct t . Id ,     year ( t . fecha )
  desde      tu_tabla t where t. fecha between p From and pHasta
  order by      t . Id , year ( t . fecha )
 ;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Fda = 1;

OPEN cur_reng;

FETCH cur_reng into xId, xAno;
WHILE Fda = 0 DO
    select min (x.fecha) into xFec     from your_table x
    WHERE x.id = xId and year (x.date) = xAno;

SET xLimI = month (xFec);

select MAX(x.fecha) into xFec
from tu_tabla x       
WHERE x.id = xId and year(x.fecha) = xAno 
;
SET xLimS= month(xFec);

insert into resulta (id, MesI, MesF, Ano)
    values (xId, xLimI, xLimS, xAno);
    FETCH cur_reng into xId, xAno;

END WHILE;

END