Получить список дат между двумя датами, используя функцию
Мой вопрос похож на этот вопрос MySQL, но предназначен для SQL Server:
Есть ли функция или запрос, который будет возвращать список дней между двумя датами? Например, допустим, есть функция ExplodeDates:
SELECT ExplodeDates('2010-01-01', '2010-01-13');
Это вернет таблицу из одного столбца со значениями:
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
Я думаю, что таблица календаря / чисел может помочь мне здесь.
Обновить
Я решил взглянуть на три предоставленных кодовых ответа, и результаты выполнения - в процентах от общего пакета -:
- Ответ Роба Фарли: 18%
- Ответ StingyJack: 41%
- КМ ответ: 41%
Ниже - лучше
Я принял ответ Роба Фарли, так как он был самым быстрым, хотя решения с таблицами чисел (используемые в их ответах как KM, так и StingyJack) - мои любимые. Роб Фарли был на две трети быстрее.
Обновление 2
Ответ Аливии гораздо лаконичнее. Я изменил принятый ответ.
21 ответ
Эти несколько строк являются простым ответом на этот вопрос в 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)
Попробуйте что-то вроде этого:
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
);
Затем вы используете:
SELECT *
FROM dbo.ExplodeDates('20090401','20090531') as d;
Отредактировано (после принятия):
Обратите внимание... если у вас уже есть достаточно большая таблица чисел, вы должны использовать:
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
);
И вы можете создать такую таблицу, используя:
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
Эти строки создадут таблицу чисел, содержащую 1M строк... и гораздо быстрее, чем вставлять их одну за другой.
Вы НЕ должны создавать свою функцию ExplodeDates, используя функцию, которая включает BEGIN и END, так как Оптимизатор запросов становится неспособным вообще упростить запрос.
Это именно то, что вы хотите, модифицированный из более раннего поста Уилла. Нет необходимости в вспомогательных таблицах или циклах.
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;
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;
Я - оракул, но я считаю, что в MS SQL Server есть поддержка предложения connect by:
select sysdate + level
from dual
connect by level <= 10 ;
Выход:
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 - это просто фиктивная таблица с оракулом (она содержит 1 строку и слово "фиктивная" в качестве значения одного столбца).
Все, что вам нужно сделать, это просто изменить жестко закодированное значение в приведенном ниже коде
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
Будут ли все эти даты уже в базе данных или вы просто хотите узнать дни между двумя датами? Если это первое, вы можете использовать BETWEEN или <= >=, чтобы найти даты между
ПРИМЕР:
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
ИЛИ ЖЕ
SELECT column_name(s)
FROM table_name
WHERE column_name
value1 >= column_name
AND column_name =< value2
Немного опоздал на вечеринку, но мне очень нравится это решение.
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
)
Несколько идей:
Если вам нужны даты в списке для их циклического просмотра, вы могли бы иметь параметры Start Date и Day Count и сделать цикл while, создавая дату и используя ее?
Используйте C# CLR хранимые процедуры и напишите код на C#
Сделайте это вне базы данных в коде
Этот запрос работает на 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;
Теперь давайте посмотрим, как это работает.
Внутренний запрос просто возвращает список целых чисел от 0 до 9999. Он даст нам диапазон 10000 значений для вычисления дат. Вы можете получить больше дат, добавив строки для десятков тысяч и сотен тысяч и так далее.
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;
Эта часть преобразует строку в дату и добавляет к ней число из внутреннего запроса.
cast('2010-01-01' as datetime) + ( a.v / 10 )
Затем мы конвертируем результат в нужный вам формат. Это также имя столбца!
format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' )
Далее мы извлекаем только отдельные значения и присваиваем имени столбца псевдоним aDate.
distinct format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) as aDate
Мы используем предложение where для фильтрации только дат в нужном диапазоне. Обратите внимание, что здесь мы используем имя столбца, поскольку SQL Server не принимает псевдоним столбца aDate в предложении where.
where format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime)
Наконец, мы сортируем результаты.
order by aDate asc;
- ### Шесть из полдюжины из другого. Другой метод, предполагающий 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;
Если вы хотите напечатать годы, начиная с определенного года до текущей даты. Просто изменил принятый ответ.
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)
Прежде чем использовать мою функцию, вам нужно настроить таблицу "помощник", вам нужно сделать это только один раз для каждой базы данных:
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
вот функция:
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
использовать этот:
select * from dbo.ListDates('2010-01-01', '2010-01-13')
выход:
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)
Возможно, если вы хотите пойти более легким путем, это должно сделать это.
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;
Но временная таблица также очень хороший подход. Возможно, вы также рассмотрите заполненную таблицу календаря.
Если вы находитесь в такой ситуации, как я, где процедуры и функции запрещены, и у вашего пользователя sql нет прав на вставку, поэтому вставка запрещена, также "установите / объявите временные переменные, такие как @c, не разрешено", но вы хотите чтобы сгенерировать список дат за определенный период, скажем, текущий год, чтобы сделать некоторую агрегацию, используйте эту
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'
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
Определенно таблица чисел, хотя вы можете использовать идею Марка Редмана о сборке / сборке CLR, если вам действительно нужна производительность.
Как создать таблицу дат (и супер быстрый способ создать таблицу чисел)
/*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
Теперь, когда у вас есть таблица дат, вы можете использовать функцию (НЕ ПРОЦЕСС), например, KM, чтобы получить таблицу из них.
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
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
Результат:
2017-09-13
2017-09-14
2017-09-15
2017-09-16
Ответ здесь avialbe Как перечислить все даты между двумя датами
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
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
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
Детали приведены в таблице ДВОЙНОЙ, но если вы обменяете эту таблицу на пустую таблицу, это работает.