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.