Obter uma lista de datas entre duas datas usando uma function

Minha pergunta é semelhante a esta questão do MySQL, mas destinada ao SQL Server:

Existe uma function ou consulta que retornará uma lista de dias entre duas datas? Por exemplo, digamos que existe uma function chamada ExplodeDates:

SELECT ExplodeDates('2010-01-01', '2010-01-13'); 

Isso retornaria uma tabela de coluna única com os 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 

Eu estou pensando que uma tabela de calendar / números pode ser capaz de me ajudar aqui.


Atualizar

Decidi dar uma olhada nas três respostas de código fornecidas e os resultados da execução – como% do lote total – são:

  • Rob Farley’s answer : 18%
  • Resposta de StingyJack : 41%
  • KM’s answer : 41%

Menor é melhor

Aceitei a resposta de Rob Farley, já que ela foi a mais rápida, mesmo que as soluções de tabela de números (usadas pelo KM e pelo StingyJack em suas respostas) sejam algo de meu favorito. Rob Farley’s foi dois terços mais rápido.

Atualização 2

A resposta de Alivia é muito mais sucinta. Eu mudei a resposta aceita.

essas poucas linhas são a resposta simples para essa pergunta no sql server.

 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) 

Tente algo assim:

 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 ); 

Você então usa:

 SELECT * FROM dbo.ExplodeDates('20090401','20090531') as d; 

Editado (após a aceitação):

Por favor, note ... se você já tem uma tabela nums suficientemente grande, então você deve 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 ); 

E você pode criar essa tabela 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 

Essas linhas criarão uma tabela de números contendo linhas de 1M ... e muito mais rápidas do que inserindo-as uma a uma.

Você NÃO deve criar sua function ExplodeDates usando uma function que envolva BEGIN e END, pois o Query Optimizer se torna incapaz de simplificar a consulta.

Isso faz exatamente o que você quer, modificado do post anterior de Will. Não há necessidade de tabelas auxiliares ou loops.

 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; 

Eu sou um cara oracle, mas acredito que o MS SQL Server tem suporte para a cláusula connect by:

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

A saída é:

 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 é apenas uma tabela 'fictícia' que vem com o oracle (contém 1 linha e a palavra 'dummy' como o valor da coluna única).

 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; 

Algumas ideias:

Se você precisar das datas da lista para percorrê-las, poderá ter os parâmetros Start Date e Day Count e fazer um loop while enquanto cria a data e a utiliza?

Use C # CLR Stored Procedures e escreva o código em C #

Faça isso fora do database no código

Todas essas datas já estarão no database ou você só quer saber os dias entre as duas datas? Se é o primeiro você poderia usar o BETWEEN ou < => = para encontrar as datas entre

EXEMPLO:

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

OU

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

Tudo o que você precisa fazer é alterar o valor codificado no código fornecido abaixo

 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 minha function, você precisa configurar uma tabela “auxiliar”, você só precisa fazer isso uma vez por database:

 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 

aqui está a function:

 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 

usa isto:

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

saída:

 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) 

Talvez se você quiser seguir um caminho mais fácil, isso deve ser feito.

 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; 

Mas a tabela temporária também é uma abordagem muito boa. Talvez você também considere uma tabela de calendar preenchida.

Definitivamente, uma tabela de números, embora você possa querer usar a idéia de Mark Redman de um proc / assembly CLR se você realmente precisar do desempenho.

Como criar a tabela de datas (e uma maneira super rápida de criar uma tabela 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 

Agora que você tem uma tabela de datas, você pode usar uma function (NOT A PROC) como KM para obter a tabela delas.

 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 

Um pouco tarde para a festa, mas eu gosto dessa solução um pouco.

 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 uma meia dúzia de outro. Outro método assumindo 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; 

Caso você queira imprimir anos a partir de um determinado ano até a data atual. Apenas alterei a resposta aceita.

 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 no 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; 

Agora vamos ver como isso funciona.

A consulta interna apenas retorna uma lista de números inteiros de 0 a 9999. Ela nos dará um intervalo de 10.000 valores para calcular datas. Você pode obter mais datas adicionando linhas para dez_milands e cem_milhares e assim por diante.

 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 converte a string em uma data e adiciona um número a ela da consulta interna.

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

Em seguida, convertemos o resultado no formato desejado. Este também é o nome da coluna!

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

Em seguida, extraímos apenas os valores distintos e fornecemos ao nome da coluna um apelido de aDate.

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

Usamos a cláusula where para filtrar apenas datas dentro do intervalo desejado. Observe que usamos o nome da coluna aqui, pois o SQL Server não aceita o alias da coluna, aDate, na cláusula where.

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

Por fim, classificamos os resultados.

  order by aDate asc; 

se você estiver em uma situação como eu, onde procedimentos e funções são proibidos , e seu usuário sql não tem permissions para inserir, portanto, inserir não permitido , também “definir / declarar variables ​​temporárias como @c não é permitido”, mas você quer para gerar uma lista de datas em um período específico , digamos, ano atual para fazer alguma agregação, use este

 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 

O detalhe está na tabela DUAL, mas se você trocar esta tabela por uma tabela falsa, isso 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 

A resposta é avialbe here Como listar todas as datas entre duas datas

 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