MSSQL 쿼리로 달력만들기

http://ddoung2.tistory.com/186

MSSQL 달력 쿼리 - 1

해당 쿼리는 어느 블로그에서 '펌'을 한것입니다. 어느 블로그인지 기억이 나지않아서 표시를 못하였습니다.

select convert(char(10),dateadd(d,number,'2007-01-01'),120) dt

from master..spt_values

where type = 'P' and number <= datediff(d,'2007-01-01','2008-12-31')

MSSQL 달력 쿼리 - 2

해당 쿼리는 최석준(MVP) 선배님님 sqlleader.com에서 답변을 달아주신 쿼리 입니다.

create table Copy_T(date char(8),year char(4),month char(2),day char(2))

 

declare @s_date char(8),@e_date char(8)

select @s_date ='20070101',@e_date = '20071231'

 

while @s_date <= @e_date

begin

           insert Copy_T values(@s_date,left(@s_date,4),substring(@s_date,5,2),right(@s_date,2))

           set @s_date = convert(char(8),dateadd(d,1,@s_date),112)

end

 

select * from Copy_T

MSSQL 달력쿼리 - 3

해당 쿼리는 예전에 DW구축을 위해서 시간차원 테이블이 필요해서 작성해본 쿼리인데 SSAS에서도 자동으로 자세하게 쿼리를 만들어주고 있습니다. *^^*

DECLARE @i      AS INT

DECLARE @NOWDATE     AS SMALLDATETIME

DECLARE @WKDATE                  AS CHAR(8)

 

 

SET      @i = 1

 

 

WHILE   @i <= 10950

           BEGIN

 

                     SET      @NOWDATE      = DATEADD(DAY, @i, '1999-12-31')

                     SET      @WKDATE                   = CONVERT(CHAR(8), @NOWDATE, 112)

 

                     INSERT INTO dbo.tbl_DimTime

                     SELECT

                                          CONVERT(DATETIME,CONVERT(CHAR(10), @NOWDATE,121))           -- 날짜

                                ,          DATENAME(DW, @NOWDATE)      ---------------------------------- 날짜에대한요일

                                ,          CASE    ---------------------------------------------------------- 한주에대한그요일의숫자

                                                     WHEN DATENAME(DW,@NOWDATE) = '일요일' THEN 1

                                                     WHEN DATENAME(DW,@NOWDATE) = '월요일' THEN 2

                                                     WHEN DATENAME(DW,@NOWDATE) = '화요일' THEN 3

                                                     WHEN DATENAME(DW,@NOWDATE) = '수요일' THEN 4

                                                     WHEN DATENAME(DW,@NOWDATE) = '목요일' THEN 5

                                                     WHEN DATENAME(DW,@NOWDATE) = '금요일' THEN 6

                                                     WHEN DATENAME(DW,@NOWDATE) = '토요일' THEN 7

                                          END

                                ,          DATEPART(DD, @NOWDATE)                  ---------------------------------- 달에대한날짜

                                ,          DATEDIFF(DD,CONVERT(CHAR(4), DATEPART(YEAR, @NOWDATE)) +'-01-01', @NOWDATE) + 1          -- 년에대한현재지난수

                                ,          DATEPART(WK, @WKDATE) - DATEPART(WK, LEFT(@WKDATE, 6) +'01') + 1                                                                              -- 달에대한몇주

                                ,          DATEPART(WK, @NOWDATE)                  ---------------------------------- 년에대한몇주

                                ,          CASE    ---------------------------------------------------------- 년에대한월영문이름

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 1 THEN'January'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 2 THEN'February'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 3 THEN'March'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 4 THEN 'April'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 5 THEN 'May'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 6 THEN 'June'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 7 THEN 'July'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 8 THEN'August'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 9 THEN'September'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 10 THEN'October'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 11 THEN'November'

                                                     WHEN   DATEPART(MONTH,@NOWDATE) = 12 THEN'December'

                                          END

                                ,          DATEPART(MONTH, @NOWDATE)            ----------------------------년에대한월숫자

                                ,          DATEPART(YEAR, @NOWDATE)                          ---------------------------- 년

                                ,          DATEPART(QQ, @NOWDATE)                                       ---------------------------- 분기

                                ,          CASE    ---------------------------------------------------------- 반기

                                                     WHEN   DATEPART(MONTH,GETDATE()) = 1

                                                                OR       DATEPART(MONTH,GETDATE()) = 2

                                                                OR       DATEPART(MONTH,GETDATE()) = 3

                                                                OR       DATEPART(MONTH,GETDATE()) = 4

                                                                OR       DATEPART(MONTH,GETDATE()) = 5

                                                                OR       DATEPART(MONTH,GETDATE()) = 6

                                                     THEN 1

                                                     WHEN   DATEPART(MONTH,GETDATE()) = 7

                                                                OR       DATEPART(MONTH,GETDATE()) = 8

                                                                OR       DATEPART(MONTH,GETDATE()) = 9

                                                                OR       DATEPART(MONTH,GETDATE()) = 10

                                                                OR       DATEPART(MONTH,GETDATE()) = 11

                                                               OR       DATEPART(MONTH,GETDATE()) = 12

                                                     THEN 2

                                          END

 

                     SET      @i = @i + 1

           END

MSSQL 날짜 쿼리 - 4

해당 쿼리는 오늘이 몇 째주에 속하는지 알아보는 쿼리 입니다.

DECLARE @WKDATE AS CHAR(8) 

SET @WKDATE = CONVERT(CHAR(8) , getdate(), 112)

SELECT DATEPART(WK, @WKDATE) - DATEPART(WK, LEFT(@WKDATE, 6) + '01') + 1