Obtenga una lista de fechas entre dos fechas usando una función

Mi pregunta es similar a esta pregunta de MySQL, pero para SQL Server:

¿Hay 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% del lote total, son:

  • La respuesta de Rob Farley : 18%
  • La respuesta de StingyJack : 41%
  • La respuesta de KM : 41%

Más bajo es mejor

He aceptado la respuesta de Rob Farley, ya que fue la más rápida, aunque las soluciones de tablas de números (usadas por KM y StingyJack en sus respuestas) son algo así como mi favorita. Rob Farley’s era dos tercios más rápido.

Actualización 2

La respuesta de Alivia es mucho más sucinta. He cambiado la respuesta aceptada.

estas pocas líneas son la respuesta simple para 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 nums suficientemente grande, entonces debe 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 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 contienen 1M filas ... y mucho más rápido que insertarlas una a una.

NO debe crear su función ExplodeDates utilizando una función que involucre BEGIN y END, ya que Query Optimizer no puede simplificar la consulta.

Esto hace exactamente lo que quieres, modificado a partir de la publicación anterior de Will. No hay necesidad de tablas auxiliares o bucles.

 WITH date_range (calc_date) AS ( SELECT DATEADD(DAY, DATEDIFF(DAY, 0, '2010-01-13') - DATEDIFF(DAY, '2010-01-01', '2010-01-13'), 0) UNION ALL SELECT DATEADD(DAY, 1, calc_date) FROM date_range WHERE DATEADD(DAY, 1, calc_date) <= '2010-01-13') SELECT calc_date FROM date_range; 

Soy un tipo oracle, pero creo que MS SQL Server tiene soporte para la cláusula connect by:

 select sysdate + level from dual connect by level <= 10 ; 

El resultado es:

 SYSDATE+LEVEL 05-SEP-09 06-SEP-09 07-SEP-09 08-SEP-09 09-SEP-09 10-SEP-09 11-SEP-09 12-SEP-09 13-SEP-09 14-SEP-09 

Dual es solo una tabla "ficticia" que viene con oracle (contiene 1 fila y la palabra "ficticio" como el valor de una sola columna).

 DECLARE @MinDate DATETIME = '2012-09-23 00:02:00.000', @MaxDate DATETIME = '2012-09-25 00:00:00.000'; SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) Dates = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate) FROM sys.all_objects a CROSS JOIN sys.all_objects b; 

Algunas ideas:

Si necesita las fechas de la lista para recorrerlas, podría tener los parámetros Fecha de inicio y Recuento de días y hacer un ciclo while mientras crea la fecha y la usa.

Use los procedimientos almacenados de C # CLR y escriba el código en C #

Haz esto fuera de la base de datos en código

¿Ya estarían todas estas fechas en la base de datos o solo quieres saber los días entre las dos fechas? Si es el primero, puede usar BETWEEN o <=> = para encontrar las fechas entre

EJEMPLO:

 SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 

O

 SELECT column_name(s) FROM table_name WHERE column_name value1 >= column_name AND column_name =< value2 

Todo lo que tiene que hacer es cambiar el valor codificado en el código proporcionado a continuación

 DECLARE @firstDate datetime DECLARE @secondDate datetime DECLARE @totalDays INT SELECT @firstDate = getDate() - 30 SELECT @secondDate = getDate() DECLARE @index INT SELECT @index = 0 SELECT @totalDays = datediff(day, @firstDate, @secondDate) CREATE TABLE #temp ( ID INT NOT NULL IDENTITY(1,1) ,CommonDate DATETIME NULL ) WHILE @index < @totalDays BEGIN INSERT INTO #temp (CommonDate) VALUES (DATEADD(Day, @index, @firstDate)) SELECT @index = @index + 1 END SELECT CONVERT(VARCHAR(10), CommonDate, 102) as [Date Between] FROM #temp DROP TABLE #temp 

Antes de usar mi función, necesita configurar una tabla de “ayuda”, solo necesita hacer esto una vez por base de datos:

 CREATE TABLE Numbers (Number int NOT NULL, CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] DECLARE @x int SET @x=0 WHILE @x<8000 BEGIN SET @x=@x+1 INSERT INTO Numbers VALUES (@x) END 

aquí está la función:

 CREATE FUNCTION dbo.ListDates ( @StartDate char(10) ,@EndDate char(10) ) RETURNS @DateList table ( Date datetime ) AS BEGIN IF ISDATE(@StartDate)!=1 OR ISDATE(@EndDate)!=1 BEGIN RETURN END INSERT INTO @DateList (Date) SELECT CONVERT(datetime,@StartDate)+n.Number-1 FROM Numbers n WHERE Number<=DATEDIFF(day,@StartDate,CONVERT(datetime,@EndDate)+1) RETURN END --Function 

utilizar esta:

 select * from dbo.ListDates('2010-01-01', '2010-01-13') 

salida:

 Date ----------------------- 2010-01-01 00:00:00.000 2010-01-02 00:00:00.000 2010-01-03 00:00:00.000 2010-01-04 00:00:00.000 2010-01-05 00:00:00.000 2010-01-06 00:00:00.000 2010-01-07 00:00:00.000 2010-01-08 00:00:00.000 2010-01-09 00:00:00.000 2010-01-10 00:00:00.000 2010-01-11 00:00:00.000 2010-01-12 00:00:00.000 2010-01-13 00:00:00.000 (13 row(s) affected) 

Quizás si deseas ir de una manera más fácil, esto debería hacerlo.

 WITH date_range (calc_date) AS ( SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - 6, 0) UNION ALL SELECT DATEADD(DAY, 1, calc_date) FROM date_range WHERE DATEADD(DAY, 1, calc_date) < CURRENT_TIMESTAMP) SELECT calc_date FROM date_range; 

Pero la tabla temporal también es un buen enfoque. Quizás también deberías considerar una tabla de calendario poblada.

Definitivamente una tabla de números, aunque puede usar la idea de Mark Redman de un proceso / ensamblado CLR si realmente necesita el rendimiento.

Cómo crear la tabla de fechas (y una manera súper rápida para crear una tabla de números)

 /*Gets a list of integers into a temp table (Jeff Moden's idea from SqlServerCentral.com)*/ SELECT TOP 10950 /*30 years of days*/ IDENTITY(INT,1,1) as N INTO #Numbers FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 /*Create the dates table*/ CREATE TABLE [TableOfDates]( [fld_date] [datetime] NOT NULL, CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED ( [fld_date] ASC )WITH FILLFACTOR = 99 ON [PRIMARY] ) ON [PRIMARY] /*fill the table with dates*/ DECLARE @daysFromFirstDateInTheTable int DECLARE @firstDateInTheTable DATETIME SET @firstDateInTheTable = '01/01/1998' SET @daysFromFirstDateInTheTable = (SELECT (DATEDIFF(dd, @firstDateInTheTable ,GETDATE()) + 1)) INSERT INTO TableOfDates SELECT DATEADD(dd,nums.n - @daysFromFirstDateInTheTable, CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date FROM #Numbers nums 

Ahora que tiene una tabla de fechas, puede usar una función (NO UN PROC) como KM para obtener la tabla de las mismas.

 CREATE FUNCTION dbo.ListDates ( @StartDate DATETIME ,@EndDate DATETIME ) RETURNS @DateList table ( Date datetime ) AS BEGIN /*add some validation logic of your own to make sure that the inputs are sound.Adjust the rest as needed*/ INSERT INTO @DateList SELECT FLD_Date FROM TableOfDates (NOLOCK) WHERE FLD_Date >= @StartDate AND FLD_Date <= @EndDate RETURN END 

Un poco tarde para la fiesta, pero me gusta bastante esta solución.

 CREATE FUNCTION ExplodeDates(@startDate DateTime, @endDate DateTime) RETURNS table as return ( SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @startDate) AS DATE FROM sys.all_objects a CROSS JOIN sys.all_objects b ) 
 Declare @date1 date = '2016-01-01' ,@date2 date = '2016-03-31' ,@date_index date Declare @calender table (D date) SET @date_index = @date1 WHILE @date_index<=@date2 BEGIN INSERT INTO @calender SELECT @date_index SET @date_index = dateadd(day,1,@date_index) IF @date_index>@date2 Break ELSE Continue END 

– ### Seis de media docena de otra. Otro método suponiendo MsSql

 Declare @MonthStart datetime = convert(DateTime,'07/01/2016') Declare @MonthEnd datetime = convert(DateTime,'07/31/2016') Declare @DayCount_int Int = 0 Declare @WhileCount_int Int = 0 set @DayCount_int = DATEDIFF(DAY, @MonthStart, @MonthEnd) select @WhileCount_int WHILE @WhileCount_int < @DayCount_int + 1 BEGIN print convert(Varchar(24),DateAdd(day,@WhileCount_int,@MonthStart),101) SET @WhileCount_int = @WhileCount_int + 1; END; 

En caso de que quiera imprimir años desde un año en particular hasta la fecha actual. Solo alteré la respuesta aceptada.

 WITH mycte AS ( SELECT YEAR(CONVERT(DATE, '2006-01-01',102)) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue + 1 < = YEAR(GETDATE()) ) SELECT DateValue FROM mycte OPTION (MAXRECURSION 0) 

Esta consulta funciona en Microsoft SQL Server.

 select distinct format( cast('2010-01-01' as datetime) + ( av / 10 ), 'yyyy-MM-dd' ) as aDate from ( SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n as v FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n) ) a where format( cast('2010-01-01' as datetime) + ( av / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime) order by aDate asc; 

Ahora veamos cómo funciona.

La consulta interna simplemente devuelve una lista de enteros de 0 a 9999. Nos dará un rango de 10,000 valores para calcular las fechas. Puede obtener más fechas agregando filas para diez_turcos y cientos_turcos, y así sucesivamente.

 SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n as v FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n) ) a; 

Esta parte convierte la cadena en una fecha y le agrega un número desde la consulta interna.

 cast('2010-01-01' as datetime) + ( av / 10 ) 

Luego convertimos el resultado en el formato que desea. ¡Este es también el nombre de la columna!

 format( cast('2010-01-01' as datetime) + ( av / 10 ), 'yyyy-MM-dd' ) 

A continuación, extraemos únicamente los valores distintos y le damos un alias de aDate al nombre de la columna.

 distinct format( cast('2010-01-01' as datetime) + ( av / 10 ), 'yyyy-MM-dd' ) as aDate 

Usamos la cláusula where para filtrar solo las fechas dentro del rango que desee. Tenga en cuenta que usamos el nombre de columna aquí porque SQL Server no acepta el alias de columna, aDate, dentro de la cláusula where.

 where format( cast('2010-01-01' as datetime) + ( av / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime) 

Por último, ordenamos los resultados.

  order by aDate asc; 

si se encuentra en una situación como yo donde los procedimientos y funciones están prohibidos , y su usuario sql no tiene permisos para insertar, por lo tanto, no se permite insertar , también “establecer / declarar variables temporales como @c no está permitido”, pero desea para generar una lista de fechas en un período específico , por ejemplo, año actual para hacer alguna agregación, use esto

 select * from (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where gen_date between '2017-01-01' and '2017-12-31' 
 WITH TEMP (DIA, SIGUIENTE_DIA ) AS (SELECT 1, CAST(@FECHAINI AS DATE) FROM DUAL UNION ALL SELECT DIA, DATEADD(DAY, DIA, SIGUIENTE_DIA) FROM TEMP WHERE DIA < DATEDIFF(DAY, @FECHAINI, @FECHAFIN) AND DATEADD(DAY, 1, SIGUIENTE_DIA) <= CAST(@FECHAFIN AS DATE) ) SELECT SIGUIENTE_DIA AS CALENDARIO FROM TEMP ORDER BY SIGUIENTE_DIA 

El detalle está en la tabla DUAL, pero si cambias esta tabla por una tabla ficticia, esto funciona.

 SELECT dateadd(dd,DAYS,'2013-09-07 00:00:00') DATES INTO #TEMP1 FROM (SELECT TOP 365 colorder - 1 AS DAYS from master..syscolumns WHERE id = -519536829 order by colorder) a WHERE datediff(dd,dateadd(dd,DAYS,'2013-09-07 00:00:00'),'2013-09-13 00:00:00' ) >= 0 AND dateadd(dd,DAYS,'2013-09-07 00:00:00') <= '2013-09-13 00:00:00' SELECT * FROM #TEMP1 

La respuesta es avialbe aquí Cómo enumerar todas las fechas entre dos fechas

 Create Procedure SelectDates(@fromDate Date, @toDate Date) AS BEGIN SELECT DATEADD(DAY,number,@fromDate) [Date] FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number,@fromDate) < @toDate END 
 DECLARE @StartDate DATE = '2017-09-13', @EndDate DATE = '2017-09-16' SELECT date FROM ( SELECT DATE = DATEADD(DAY, rn - 1, @StartDate) FROM ( SELECT TOP (DATEDIFF(DAY, @StartDate, DATEADD(DAY,1,@EndDate))) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ORDER BY s1.[object_id] ) AS x ) AS y 

Resultado:

 2017-09-13 2017-09-14 2017-09-15 2017-09-16 
    Intereting Posts