함수를 사용하여 두 날짜 사이의 날짜 목록 가져 오기
내 질문은 이 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
달력 / 숫자 표가 여기에서 도움이 될 수 있다고 생각합니다.
최신 정보
제공된 세 가지 코드 답변을 살펴보기로 결정했으며 실행 결과 (총 배치의 %)는 다음과 같습니다.
- Rob Farley의 답변 : 18 %
- StingyJack의 답변 : 41 %
- KM 답변 : 41 %
낮을수록 좋습니다
숫자 테이블 솔루션 (KM과 StingyJack이 답변에 사용)이 제가 가장 좋아하는 것임에도 불구하고 Rob Farley의 답변이 가장 빠르기 때문에 수락했습니다. Rob Farley는 2/3 더 빨랐습니다.
업데이트 2
Alivia의 대답 은 훨씬 더 간결합니다. 수락 된 답변을 변경했습니다.
이 몇 줄은 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;
수정 됨 (승인 후) :
참고 ... 이미 충분히 큰 nums 테이블이있는 경우 다음을 사용해야합니다.
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 행을 포함하는 숫자 테이블을 생성하며 하나씩 삽입하는 것보다 훨씬 빠릅니다.
Query Optimizer가 쿼리를 전혀 단순화 할 수 없게되므로 BEGIN 및 END가 포함 된 함수를 사용하여 ExplodeDates 함수를 생성해서는 안됩니다.
이것은 Will의 이전 게시물에서 수정 한 원하는대로 정확하게 수행합니다. 도우미 테이블이나 루프가 필요하지 않습니다.
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;
저는 오라클 사용자이지만 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은 oracle과 함께 제공되는 '더미'테이블입니다 (단일 열의 값으로 1 개의 행과 'dummy'라는 단어가 포함되어 있음).
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;
몇 가지 아이디어 :
날짜를 반복하기 위해 목록 날짜가 필요한 경우 시작 날짜 및 날짜 수 매개 변수가 있고 날짜를 작성하고 사용하는 동안 while 루프를 수행 할 수 있습니다.
C # CLR 저장 프로 시저를 사용하고 C #으로 코드 작성
코드에서 데이터베이스 외부에서 수행
이 모든 날짜가 이미 데이터베이스에 있습니까? 아니면 두 날짜 사이의 날짜 만 알고 싶습니까? 첫 번째 경우 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
아래에 제공된 코드에서 하드 코딩 된 값을 변경하기 만하면됩니다.
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
내 함수를 사용하기 전에 "도우미"테이블을 설정해야합니다.이 작업은 데이터베이스 당 한 번만 수행하면됩니다.
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;
그러나 임시 테이블도 매우 좋은 접근 방식입니다. 아마도 채워진 달력 테이블도 고려할 것입니다.
실제로 성능이 필요한 경우 Mark Redman의 CLR proc / assembly 아이디어를 사용하고 싶을 수 있지만 확실히 숫자 테이블입니다.
날짜 표를 만드는 방법 (그리고 숫자 표를 만드는 매우 빠른 방법)
/*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과 같은 함수 (PROC 아님)를 사용하여 테이블을 가져올 수 있습니다.
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
파티에 조금 늦었지만 나는이 솔루션을 꽤 좋아합니다.
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
-### 6 개 중 6 개. 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)
이 쿼리는 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까지의 정수 목록 만 반환합니다. 날짜 계산을위한 10,000 개의 값 범위를 제공합니다. 10 만 및 10 만 등의 행을 추가하여 더 많은 날짜를 얻을 수 있습니다.
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 )
Then we convert the result into the format you want. This is also the column name!
format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' )
Next we extract only the distinct values and give the column name an alias of aDate.
distinct format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) as aDate
We use the where clause to filter in only dates within the range you want. Notice that we use the column name here since SQL Server does not accept the column alias, aDate, within the where clause.
where format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime)
Lastly, we sort the results.
order by aDate asc;
if you're in a situation like me where procedures and functions are prohibited, and your sql user does not have permissions for insert, therefore insert not allowed, also "set/declare temporary variables like @c is not allowed", but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this
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
The detail is on the table DUAL but if your exchange this table for a dummy table this works.
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 is avialbe here How to list all dates between two dates
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
Result:
2017-09-13
2017-09-14
2017-09-15
2017-09-16
참고URL : https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function
'code' 카테고리의 다른 글
| 비 활동 클래스에서 활동을 시작하려면 어떻게해야합니까? (0) | 2020.11.04 |
|---|---|
| c #의 iif에 해당 (0) | 2020.11.03 |
| 자신 만의 제곱근 함수 작성 (0) | 2020.11.03 |
| xdebug가 작동하는지 확인 (0) | 2020.11.03 |
| Firebase onTokenRefresh ()가 호출되지 않았습니다. (0) | 2020.11.03 |