Generar fechas entre intervalos de fechas
Necesito rellenar una tabla que almacenará los rangos de fechas entre 2 fechas dadas: 09/01/11-10/10/11
Entonces, en este caso, la tabla comenzaría desde el 09/01/11 y se almacenaría cada día hasta que llegara al 10/10/11 Me preguntaba si había una forma ingeniosa de hacer esto en SQL Server - actualmente estoy usando SQL Server 2008. Gracias
8 answers
Fácil en SQL 2005+; más fácil si tiene una tabla numbers o tally. Lo fingí a continuación:
DECLARE @StartDate DATE = '20110901'
, @EndDate DATE = '20111001'
SELECT DATEADD(DAY, nbr - 1, @StartDate)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
Si tiene una tabla de tally, reemplace la subconsulta con la tabla. Sin recursión.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-19 16:55:12
Pruebe esto si está utilizando SQL Server 2005 o posterior:
WITH Dates AS (
SELECT
[Date] = CONVERT(DATETIME,'09/01/2011')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '10/10/2011'
) SELECT
[Date]
FROM
Dates
OPTION (MAXRECURSION 45)
Un buen ejemplo de cosas interesantes que puedes hacer con un CTE.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-19 16:43:59
-- Declaraciones
DECLARE @dates TABLE(dt datetime)
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'
-- Consulta:
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END
-- Salida
SELECT * FROM @dates
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-19 16:45:54
Use la función F_TABLE_DATE de MVJ, es puramente impresionante:
Http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Una vez que implemente esto, simplemente pase la fecha de inicio y finalización y puede insertar todas las fechas entre.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-19 16:41:48
Aquí hay una solución que no requiere recursividad, y al mismo tiempo, esta función con valor de tabla es reutilizable en muchas consultas sin la necesidad de repetir la declaración de variables repetitivas de nuevo. Esta es la única alternativa, para aquellos que no quieren recursión.
Crea esta sencilla función:
CREATE FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
@EndDate AS DATE,
@Interval AS INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
DECLARE @CUR_DATE DATE
SET @CUR_DATE = @StartDate
WHILE @CUR_DATE <= @EndDate BEGIN
INSERT INTO @Dates VALUES(@CUR_DATE)
SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
END
RETURN;
END;
Y luego seleccione por:
select *
from dbo.GenerateDateRange('2017-01-03', '2017-12-01', 1)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-02-11 08:03:02
Usando la respuesta de @Abe Miesler, para mayor comodidad de los demás lo construí en un TVF para SQL Server 2008 en adelante. Puede ayudar a otros - ¡Tuve que encontrar una manera de incluir el CTE dentro del TVF!
--Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
ALTER FUNCTION [dbo].[DateRange]
(@startDate AS DATE,
@EndDate AS DATE,
@interval AS INT
)
RETURNS @Dates TABLE(dateValue DATE)
AS
BEGIN
WITH Dates
AS (
SELECT [Date] = CONVERT( DATETIME, @startDate)
UNION ALL
SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
FROM Dates
WHERE Date < @EndDate)
INSERT INTO @Dates
SELECT [Date]
FROM Dates
OPTION(MAXRECURSION 900);
RETURN;
END;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-08-12 10:07:18
Declare @StartDate datetime = '2015-01-01'
Declare @EndDate datetime = '2016-12-01'
declare @DaysInMonth int
declare @tempDateRange Table
(
DateFrom datetime,
DateThru datetime
);
While @StartDate<=@EndDate
begin
SET @DaysInMonth=DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@StartDate),0)))
IF DAY(@StartDate)=1
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
SET @EndDate=DATEADD(DAY,15,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
SET @EndDate=DATEADD(DAY,12,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
SET @EndDate=DATEADD(DAY,13,@StartDate)
INSERT INTO @tempDateRange (DateFrom,DateThru)
VALUES
(
@StartDate,
@EndDate
)
SET @StartDate=DATEADD(DAY,1,@EndDate)
IF @EndDate< '2016-12-31'
IF DAY(@StartDate)=1
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
SET @EndDate=DATEADD(DAY,15,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
SET @EndDate=DATEADD(DAY,12,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
SET @EndDate=DATEADD(DAY,13,@StartDate)
end ;
select * from @tempDateRange
+++++++++++++++++++++++++++++
Result:
DateFrom |DateThru
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-01-13 19:25:54
Si por alguna razón no puedes declare
variables, como cuando usas tablas derivadas en Looker , puedes hacer lo siguiente:
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(
day,
convert(date, '2017-01-01'),
convert(date, '2018-12-31')
)
Por cierto, así es como tu vista de la serie de fechas podría verse en LookerML:
view: date_series {
derived_table: {
sql:
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
}
dimension: date {
primary_key: yes
type: date
sql: ${TABLE}.d ;;
}
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-11 19:17:30