MSSQL 의 spt_values 활용으로 특정 시작일과 종료일 사이 추출

MSSQL 의 특정 시작일과 종료일 사이의 일자 추출하기

DECLARE @S_DATE smalldatetime,@E_DATE smalldatetime
SET @S_DATE = CONVERT(smalldatetime, '2019-02-01') -- 특정월 시작일자
SET @E_DATE = DATEADD(day, -1,DATEADD(MONTH, 1,DATENAME(YEAR,@S_DATE) + DATENAME(month,@S_DATE)+'01')) -- 특정월 종료일자

SELECT CONVERT(CHAR(10), DATEADD(d, NUMBER, @S_DATE),120) AS DT
FROM MASTER..SPT_VALUES WITH(NOLOCK)
WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, @S_DATE), 120) <= @E_DATE

 

 

---

위와 동일하게 결과가 나옵니다. 차이점은 날짜 형식으로 범위를 찾지 않고 년월일, yyyyMMdd 인 숫자 8자리 형태로 검색을 합니다. 즉, 20190201 ~ 20190228 와 같은 int 형으로도 검색이 가능합니다.

DECLARE @S_DATE smalldatetime,@E_DATE smalldatetime
SET @S_DATE = CONVERT(smalldatetime, '2019-02-01') -- 특정월 시작일자
SET @E_DATE = DATEADD(day, -1,DATEADD(MONTH, 1,DATENAME(YEAR,@S_DATE) + DATENAME(month,@S_DATE)+'01')) -- 특정월 종료일자

SELECT CONVERT(VARCHAR(10), DATEADD(d, NUMBER, format(@S_DATE,'yyyyMMdd')),120) AS DT
FROM MASTER..SPT_VALUES
WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, format(@S_DATE,'yyyyMMdd'), format(@E_DATE,'yyyyMMdd'))

---

spt_values 의 다양한 사용방법

https://www.sqlindia.com/tricks-using-system-table-spt_values-sql-server/

Tricks using system table spt_values – SQL Server

 

 

SQL Server에서 master.dbo.spt_values ​​테이블의 목적은 무엇입니까? 솔직히 모르겠지만 쿼리 에서이 테이블을 많이 사용합니다. 이 테이블은 마스터 데이터베이스에 상주하며“number”라는 열에 0에서 2047 사이의 시퀀스 번호가 있으며“P”유형 ( SELECT number FROM master..spt_values ​​여기서 type = 'p')입니다. 지금까지이 표에 대한 MSDN에는 사용 가능한 문서가 없습니다. 문제는이 테이블이 어떻게 우리에게 유용할까요? 이것은 숫자 순서를 가지고 있기 때문에 반복, 재귀 등에 도움이 될 것입니다. 대부분의 데이터베이스에서 수동으로 작성된 간단한 숫자 표를 보았을 것입니다. 데이터베이스에 데이터베이스가 없으면 걱정할 필요가 없습니다. 마스터 데이터베이스에 Microsoft가 있습니다. SQL Server에서 spt_values ​​테이블을 사용하여 수행 할 수있는 몇 가지 트릭에 대해 설명하겠습니다.

 

 

Trick 01 : 문자열에서 문자, 숫자 및 특수 문자 찾기

DECLARE @table table (id int identity(1,1), value nvarchar(100))
INSERT INTO @table
VALUES ('B65bfNM#*amF5*t'), ('4GZ_3h^2STJTZVG')
, ('4%bKU&hxgj7!3!t'), ('uX=g3PE+w&uVV!h')

--Find only English letters
SELECT
	(SELECT
		SUBSTRING(value, number, 1)
	FROM master..spt_values
	WHERE SUBSTRING(value, number, 1) LIKE '[A-Z]' AND type = 'P'
	FOR xml PATH (''))
FROM @table

--Find only numbers
SELECT
	(SELECT
		SUBSTRING(value, number, 1)
	FROM master..spt_values
	WHERE SUBSTRING(value, number, 1) LIKE '[0-9]' AND type = 'P'
	FOR xml PATH (''))
FROM @table

--Find only special characters
SELECT
	(SELECT
		SUBSTRING(value, number, 1)
	FROM master..spt_values
	WHERE SUBSTRING(value, number, 1) LIKE '[^0-9]'
	AND SUBSTRING(value, number, 1) LIKE '[^A-Z]' AND type = 'P'
	FOR xml PATH (''))
FROM @table

 

Trick 02 : 한 해의 모든 월 이름 생성

SELECT TOP 12
DATENAME(MONTH, cast('2015 -'+ LTRIM(RTRIM(CAST(number as varchar(2)))) + '-1' as datetime)) [MonthName]
FROM master..spt_values
WHERE Type = 'P' and number > 0
ORDER BY Number

 

트릭 03 : 쉼표로 구분 된 문자열 분리

DECLARE @var VARCHAR(100)
SET @var = 'I,Love,To,Share,My,Knowledge,On,SQLIndia.com' 

SELECT
	SUBSTRING
	(
	',' + @var + ',',
	Number + 1,
	CHARINDEX
	(
	',',
	',' + @var + ',',
	Number + 1
	) - Number - 1
	) as value
FROM master..spt_values
WHERE Number >= 1
AND Number < LEN(',' + @var + ',') - 1
AND SUBSTRING(',' + @var + ',', Number, 1) = ','
AND type = 'P'
ORDER BY Number

 

트릭 o4 : 두 날짜 사이의 날짜 순서 찾기

DECLARE @startDate SMALLDATETIME
, @endDate SMALLDATETIME
SET @startDate = '20150101'
SET @endDate = '20150115' 

SELECT
	@startDate + Number as [Date],
	DATENAME(dw, @startDate + Number) as [dtName]
FROM master..spt_values
WHERE @startDate + Number <= @endDate AND type = 'P'
ORDER BY 1

 

자주 묻는 인터뷰 질문 :  한 달의 첫 번째 토요일과 마지막 토요일 찾기 .

 

트릭 05 : 테이블에서 아이덴티티 갭 찾기

DECLARE @table table (id int identity(1,1), value nvarchar(100))
INSERT INTO @table
VALUES ('B65bfNM#*amF5*t'), ('4GZ_3h^2STJTZVG')
, ('4%bKU&hxgj7!3!t'), ('uX=g3PE+w&uVV!h')
, ('4%bKU&hxgt'), ('uX=g3PVV!h')

DELETE FROM @table WHERE id IN (3, 5)

SELECT * FROM @table

SELECT Number
FROM master..spt_values
WHERE Number NOT IN (SELECT ID FROM @table)
AND Number < (SELECT MAX(ID) FROM @table) and type='P' and number > 0
ORDER BY Number

거대한 테이블이 있고 ID 값이 2047보다 큰 경우이 방법은 시퀀스가 ​​2047까지이므로 작동하지 않습니다