Obtener una lista de fechas entre dos fechas

Para MSSQL puede usar esto. Es MUY rápido.

Puede envolver esto en una función con valor de tabla o un proc almacenado y analizar las fechas de inicio y finalización como variables.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO

Puedes usar las variables de usuario de MySQL así:

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num es -1 porque lo agregas la primera vez que lo usas. Además, no puede usar "TENER date_sequence" porque eso hace que la variable de usuario se incremente dos veces por cada fila.

Tuvimos un problema similar con los informes BIRT en que queríamos informar sobre aquellos días que no tenían datos. Como no había entradas para esas fechas, la solución más fácil para nosotros fue crear una tabla simple que almacenara todas las fechas y usarla para obtener rangos o unirse para obtener valores cero para esa fecha.

Tenemos un trabajo que se ejecuta todos los meses para garantizar que la tabla se rellene 5 años en el futuro. La tabla se crea así:

create table all_dates (
    dt date primary key
);

Sin duda hay truco mágico formas de hacer esto con diferentes DBMS, pero siempre optamos por la solución más simple. Los requisitos de almacenamiento para la tabla son mínimos y hace que las consultas sean mucho más simples y portátiles. Este tipo de solución es casi siempre mejor desde el punto de vista del rendimiento, ya que no requiere cálculos por fila en los datos.

La otra opción (y hemos usado esto antes) es asegurar que haya una entrada en la tabla para cada fecha. Barrimos la tabla periódicamente y añadimos cero entradas para fechas y / o horas que no existían. Esto puede no ser una opción en su caso, depende de los datos almacenados.

Si realmente cree que es una molestia mantener la tabla all_dates poblada, un procedimiento almacenado es el camino a seguir que devolverá un conjunto de datos que contenga esas fechas. Esto es casi seguro que será más lento, ya que tienes que calcular el rango cada vez que se llama en lugar de simplemente extraer datos pre-calculados de una tabla.

Pero, para ser honesto, usted podría poblar la tabla sale por 1000 años sin ningún problema serio de almacenamiento de datos: 365,000 fechas de 16 bytes (por ejemplo) más un índice que duplica la fecha más un 20% de sobrecarga por seguridad, estimaría aproximadamente 14 millones [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), una tabla minúscula en el esquema de las cosas.

Tomando prestada una idea de esta respuesta, puede configurar una tabla de 0 a 9 y usarla para generar su lista de fechas.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';

Esto le permitirá generar una lista de hasta 1000 fechas. Si necesita ir más grande, puede agregar otra combinación cruzada a la consulta interna.

Para el Acceso (o cualquier lenguaje SQL)

1. Cree una tabla que tenga 2 campos, llamaremos a esta tabla tempRunDates:
   --Campos fromDate y toDate
   -- A continuación, inserte solo 1 registro, que tiene la fecha de inicio y la fecha de finalización.

2. Crear otra tabla: Time_Day_Ref
   -- Importe una lista de fechas (hacer lista en excel es fácil) en esta tabla.
   -- El nombre del campo en mi caso es Greg_Dt, para la fecha Gregoriana
   -- Hice mi lista desde el 1 de enero de 2009 hasta el 1 de enero 2020.

3. Ejecute la consulta:

   SELECT Time_Day_Ref.GREG_DT
   FROM tempRunDates, Time_Day_Ref
   WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;
   

Fácil!

Normalmente uno usaría una tabla de números auxiliares que usualmente mantiene para este propósito con alguna variación sobre esto:

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

He visto variaciones con funciones con valores de tabla, etc.

También puede mantener una lista permanente de fechas. Tenemos eso en nuestro almacén de datos, así como una lista de horas del día.

Bien, cómo encontrar fechas entre dos fechas dadas en SQL server se explica en http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html

CREATE FUNCTION [dbo].[_DATES]
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS 
@DATES TABLE(
    DATE1 DATETIME
)
AS
BEGIN
    WHILE @startDate <= @endDate
    BEGIN 
        INSERT INTO @DATES (DATE1)
            SELECT @startDate   
    SELECT @startDate = DATEADD(d,1,@startDate) 
    END
RETURN
END

Utilizamos esto en nuestro sistema HRMS, lo encontrará útil

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days

Esta solución funciona con MySQL 5.0
Crear una tabla - mytable.
El esquema no es material. Lo que importa es el número de filas en él.
Por lo tanto, puede mantener solo una columna de tipo INT con 10 filas, valores - 1 a 10.

SQL:

set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= '2011-07-31';

Limitación: El número máximo de fechas devueltos por la consulta anterior será
(rows in mytable)*(rows in mytable) = 10*10 = 100.

Puede aumentar este rango cambiando forma parte en sql:
desde mytable x, mytable y, mytable z
Por lo tanto, el rango ser 10*10*10 =1000 y así sucesivamente.

Cree un procedimiento almacenado que tome dos parámetros a_begin y a_end. Cree una tabla temporal dentro de ella llamada t, declare una variable d, asigne a_begin a d, y ejecute un bucle WHILE INSERTing d en t y llame a la función ADDDATE para incrementar el valor d. Finalmente SELECT * FROM t.

Usaría algo similar a esto:

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

Entonces la tabla variable @HOLDER contiene todas las fechas incrementadas por día entre esas dos fechas, listas para unirse al contenido de sus corazones.

He estado luchando con esto durante bastante tiempo. Dado que este es el primer éxito en Google cuando busqué la solución, déjame publicar donde he llegado hasta ahora.

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

Reemplace [yourTable] con una tabla de su base de datos. El truco es que el número de filas en la tabla que seleccione debe ser >= el número de fechas que desea devolver. Intenté usar el marcador de posición de tabla DUAL, pero solo devolvería una sola fila.

DELIMITER $$  
CREATE PROCEDURE popula_calendario_controle()
   BEGIN
      DECLARE a INT Default 0;
      DECLARE first_day_of_year DATE;
      set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
      one_by_one: LOOP
         IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
            INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
         END IF;
         SET a=a+1;
         IF a=365 THEN
            LEAVE one_by_one;
         END IF;
      END LOOP one_by_one;
END $$

Este procedimiento insertará todas las fechas desde el comienzo del año hasta ahora, solo sustituya los días de "inicio" y "fin", ¡y estará listo para comenzar!

Necesitaba una lista con todos los meses entre 2 fechas para las estadísticas. Las 2 fechas son la fecha de inicio y finalización de una suscripción. Así que la lista muestra todos los meses y la cantidad de suscripciones por mes.

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
   -- Select the ultimate start and enddate from subscribers
   select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")), 
          @enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
   from subscription a;

   -- Tmp table with all months (dates), you can always format them with DATE_FORMAT) 
   DROP TABLE IF EXISTS tmp_months;
   create temporary table tmp_months (
      year_month date,
      PRIMARY KEY (year_month)
   );


   set @tempDate=@startdate;  #- interval 1 MONTH;

   -- Insert every month in tmp table
   WHILE @tempDate <= @enddate DO
     insert into tmp_months (year_month) values (@tempDate);
     set @tempDate = (date(@tempDate) + interval 1 MONTH);
   END WHILE;

   -- All months
   select year_month from tmp_months;

   -- If you want the amount of subscription per month else leave it out
   select mnd.year_month, sum(subscription.amount) as subscription_amount
   from tmp_months mnd
   LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
   GROUP BY mnd.year_month;

 END

Estoy usando Server version: 5.7.11-log MySQL Community Server (GPL)

Ahora vamos a resolver esto de una manera sencilla.

He creado una tabla llamada "datetable"

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid   | int(11) | NO   | PRI | NULL    |       |
| coldate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Ahora, wee verá los registros insertados dentro.

mysql> select * from datetable;
+-------+------------+
| colid | coldate    |
+-------+------------+
|   101 | 2015-01-01 |
|   102 | 2015-05-01 |
|   103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

Y aquí nuestra consulta para obtener registros dentro de dos fechas en lugar de esas fechas.

mysql> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate    |
+-------+------------+
|   102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

Espero que esto ayude a muchos.

select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

Aquí, primero agregue un día a la fecha final actual, será 2011-02-28 00:00:00, luego reste un segundo para hacer la fecha final 2011-02-27 23:59:59. Al hacer esto, puede obtener todas las fechas entre los intervalos dados.

Salida:
2011/02/25
2011/02/26
2011/02/27