sql server - powerapps - Obtenga una lista de fechas entre dos fechas usando una función

powerapps ejemplos

Question

Mi pregunta es similar a this 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 answer de Alivia es mucho más sucinta. He cambiado la respuesta aceptada.

Answer 1

¿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

Answer 2

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

Answer 3

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

Answer 4

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)

Answer 5

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

Answer 6

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)

Answer 7

Esta consulta funciona en Microsoft SQL Server.

select distinct format( cast(''2010-01-01'' as datetime) + ( a.v / 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) + ( a.v / 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) + ( a.v / 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) + ( a.v / 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) + ( a.v / 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) + ( a.v / 10 ), ''yyyy-MM-dd'' ) < cast(''2010-01-13'' as datetime)

Por último, ordenamos los resultados.

 order by aDate asc;

Answer 8

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;

Answer 9

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

Answer 10

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.

Answer 11

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.

Answer 12

Soy un tipo oráculo, 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 oráculo (contiene 1 fila y la palabra "ficticio" como el valor de una sola columna).

Answer 13

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

Answer 14

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
)

Answer 15

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)

Answer 16

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''

Answer 17

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;

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

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.