MSSQL Calendar 달력 관련 테이블 스키마

2019.01.01 부터 2076.05.10 까지 출력되는 구문입니다.

 

 

테이블 스키마

CREATE TABLE [dbo].[UNIV_COM_Calendar](
	[CalendarID] [bigint] IDENTITY(1,1) NOT NULL,
	[CalendarDate] [datetime] NULL,
	[CalendarDayWeekEng] [varchar](50) NULL,
	[CalendarDayWeekDigit] [int] NULL,
	[CalendarDay] [int] NULL,
	[CalendarAcmltDay] [int] NULL,
	[CalendarWeek] [int] NULL,
	[CalendarAcmltWeek] [int] NULL,
	[CalendarMonthEng] [varchar](50) NULL,
	[CalendarMonth] [int] NULL,
	[CalendarYear] [int] NULL,
	[CalendarFourQuarter] [int] NULL,
	[CalendarTwoQuarter] [int] NULL
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'내부코드아이디' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'날짜' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'요일(영문)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarDayWeekEng'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'요일(1~7)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarDayWeekDigit'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'일' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarDay'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'누적일수' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarAcmltDay'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'월간주일' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarWeek'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'누적 월간주일' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarAcmltWeek'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'월(영문)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarMonthEng'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'월' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarMonth'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'년' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarYear'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1~4분기' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarFourQuarter'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1~2분기' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UNIV_COM_Calendar', @level2type=N'COLUMN',@level2name=N'CalendarTwoQuarter'
GO

실행 구문

DECLARE @i AS INT 
DECLARE @NOWDATE AS SMALLDATETIME 
DECLARE @WKDATE AS CHAR(8) 

SET @i = 1 

WHILE @i <= 20950 
  BEGIN 
      SET @NOWDATE = Dateadd(day, @i, '2018-12-31') 
      SET @WKDATE = CONVERT(CHAR(8), @NOWDATE, 112) 

	  --insert into univ_com_calendar
      SELECT CONVERT(DATETIME, CONVERT(CHAR(10), @NOWDATE, 121)), 

			 CASE 
               WHEN Datename(dw, @NOWDATE) = N'일요일' THEN 'Sunday' 
               WHEN Datename(dw, @NOWDATE) = N'월요일' THEN 'Monday' 
               WHEN Datename(dw, @NOWDATE) = N'화요일' THEN 'Tuesday' 
               WHEN Datename(dw, @NOWDATE) = N'수요일' THEN 'Wednesday' 
               WHEN Datename(dw, @NOWDATE) = N'목요일' THEN 'Thursday' 
               WHEN Datename(dw, @NOWDATE) = N'금요일' THEN 'Friday' 
               WHEN Datename(dw, @NOWDATE) = N'토요일' THEN 'Saturday'
             END, 
             CASE 
               WHEN Datename(dw, @NOWDATE) = N'일요일' THEN 1 
               WHEN Datename(dw, @NOWDATE) = N'월요일' THEN 2 
               WHEN Datename(dw, @NOWDATE) = N'화요일' THEN 3 
               WHEN Datename(dw, @NOWDATE) = N'수요일' THEN 4 
               WHEN Datename(dw, @NOWDATE) = N'목요일' THEN 5 
               WHEN Datename(dw, @NOWDATE) = N'금요일' THEN 6 
               WHEN Datename(dw, @NOWDATE) = N'토요일' 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 N'January' 
               WHEN Datepart(month, @NOWDATE) = 2 THEN N'February' 
               WHEN Datepart(month, @NOWDATE) = 3 THEN N'March' 
               WHEN Datepart(month, @NOWDATE) = 4 THEN N'April' 
               WHEN Datepart(month, @NOWDATE) = 5 THEN N'May' 
               WHEN Datepart(month, @NOWDATE) = 6 THEN N'June' 
               WHEN Datepart(month, @NOWDATE) = 7 THEN N'July' 
               WHEN Datepart(month, @NOWDATE) = 8 THEN N'August' 
               WHEN Datepart(month, @NOWDATE) = 9 THEN N'September' 
               WHEN Datepart(month, @NOWDATE) = 10 THEN N'October' 
               WHEN Datepart(month, @NOWDATE) = 11 THEN N'November' 
               WHEN Datepart(month, @NOWDATE) = 12 THEN N'December' 
             END, 
             Datepart(month, @NOWDATE), 
             Datepart(year, @NOWDATE), 
             Datepart(qq, @NOWDATE), 
             CASE 
               WHEN Datepart(month, @NOWDATE) = 1 
                     OR Datepart(month, @NOWDATE) = 2 
                     OR Datepart(month, @NOWDATE) = 3 
                     OR Datepart(month, @NOWDATE) = 4 
                     OR Datepart(month, @NOWDATE) = 5 
                     OR Datepart(month, @NOWDATE) = 6 THEN 1 
               WHEN Datepart(month, @NOWDATE) = 7 
                     OR Datepart(month, @NOWDATE) = 8 
                     OR Datepart(month, @NOWDATE) = 9 
                     OR Datepart(month, @NOWDATE) = 10 
                     OR Datepart(month, @NOWDATE) = 11 
                     OR Datepart(month, @NOWDATE) = 12 THEN 2 
             END 

      SET @i = @i + 1 
  END 

결과물

참고사이트
https://ddoung2.tistory.com/186

MSSQL 쿼리로 달력만들기