Obtenga una lista de fechas entre dos fechas usando una función
Mi pregunta es similar a esta pregunta de MySQL, pero destinada a SQL Server:
¿Existe alguna función o consulta que devuelva una lista de días entre dos fechas? Por ejemplo, digamos que hay una función llamada ExplodeDates:
SELECT ExplodeDates('2010-01-01', '2010-01-13');
Esto devolvería una tabla de una sola columna con los valores:
2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
2010-01-11
2010-01-12
2010-01-13
Estoy pensando que una tabla de calendario/números podría ayudarme aquí.
Actualizar
Decidí echar un vistazo a las tres respuestas de código proporcionadas y los resultados de la ejecución, como porcentaje del lote total, son:
- Respuesta de Rob Farley : 18%
- Respuesta de StingyJack : 41%
- La respuesta de KM : 41%
Más bajo es mejor
Acepté la respuesta de Rob Farley, ya que era la más rápida, aunque las soluciones de tablas de números (utilizadas tanto por KM como por StingyJack en sus respuestas) son una de mis favoritas. El de Rob Farley fue dos tercios más rápido.
Actualización 2
La respuesta de Alivia es mucho más concisa. He cambiado la respuesta aceptada.
Estas pocas líneas son la respuesta simple a esta pregunta en el servidor SQL.
WITH mycte AS
(
SELECT CAST('2011-01-01' AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < '2021-12-31'
)
SELECT DateValue
FROM mycte
OPTION (MAXRECURSION 0)
Pruebe algo como esto:
CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with
N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);
Luego usas:
SELECT *
FROM dbo.ExplodeDates('20090401','20090531') as d;
Editado (después de la aceptación):
Tenga en cuenta... si ya tiene una tabla de números lo suficientemente grande, entonces debería usar:
CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);
Y puedes crear una tabla de este tipo usando:
CREATE TABLE dbo.nums (num int PRIMARY KEY);
INSERT dbo.nums values (1);
GO
INSERT dbo.nums SELECT num + (SELECT COUNT(*) FROM nums) FROM nums
GO 20
Estas líneas crearán una tabla de números que contiene 1 millón de filas... y mucho más rápido que insertarlas una por una.
NO debe crear su función ExplodeDates usando una función que implique COMENZAR y FINALIZAR, ya que el Optimizador de consultas no puede simplificar la consulta en absoluto.
