首页 > 代码库 > DimDate
DimDate
EXEC PopulateDimDate ‘2005-01-01‘,‘2026-12-31‘,-4CREATE TABLE [dbo].[DimDate]( [DateKey] int NOT NULL , [FullDate] DATE NOT NULL , [MonthNumberOfYear] tinyint NOT NULL , [MonthNumberOfQuarter] tinyint NOT NULL , [ISOYearAndWeekNumber] char(7) NOT NULL , [ISOWeekNumberOfYear] tinyint NOT NULL , [SSWeekNumberOfYear] tinyint NOT NULL , [ISOWeekNumberOfQuarter_454_Pattern] tinyint NOT NULL , [SSWeekNumberOfQuarter_454_Pattern] tinyint NOT NULL , [SSWeekNumberOfMonth] tinyint NOT NULL , [DayNumberOfYear] smallint NOT NULL , [DaysSince1900] int NOT NULL , [DayNumberOfFiscalYear] smallint NOT NULL , [DayNumberOfQuarter] smallint NOT NULL , [DayNumberOfMonth] tinyint NOT NULL , [DayNumberOfWeek_Sun_Start] tinyint NOT NULL , [MonthName] varchar(10) NOT NULL , [MonthNameAbbreviation] char(3) NOT NULL , [DayName] varchar(10) NOT NULL , [DayNameAbbreviation] varchar(10) NOT NULL , [CalendarYear] smallint NOT NULL , [CalendarYearMonth] char(7) NOT NULL , [CalendarYearQtr] char(7) NOT NULL , [CalendarSemester] tinyint NOT NULL , [CalendarQuarter] tinyint NOT NULL , [FiscalYear] smallint NOT NULL , [FiscalMonth] tinyint NOT NULL , [FiscalQuarter] tinyint NOT NULL , [FiscalYearMonth] char(7) NOT NULL , [FiscalYearQtr] char(8) NOT NULL , [QuarterNumber] int NOT NULL , [YYYYMMDD] char(8) NOT NULL , [MM/DD/YYYY] char(10) NOT NULL , [YYYY/MM/DD] char(10) NOT NULL , [YYYY-MM-DD] char(10) NOT NULL , [MonDDYYYY] char(11) NOT NULL , [IsLastDayOfMonth] char(1) NOT NULL , [IsWeekday] char(1) NOT NULL , [IsWeekend] char(1) NOT NULL , [IsWorkday] char(1) NOT NULL DEFAULT ‘N‘ , [IsFederalHoliday] char(1) NOT NULL DEFAULT ‘N‘ , [IsBankHoliday] char(1) NOT NULL DEFAULT ‘N‘ , [IsCompanyHoliday] char(1) NOT NULL DEFAULT ‘N‘ , [PeriodYearMonth] NVARCHAR(12) NOT NULL , CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED ([DateKey] ASC) )GOCREATE PROCEDURE dbo.PopulateDimDate @starting_dt DATE , @ending_dt DATE , @FiscalYearMonthsOffset INTAS SET NOCOUNT ON SET DATEFIRST 7 -- Standard for U.S. Week starts on Sunday DECLARE @Yr INT , @EndYr INT SET @Yr = DATEPART(yyyy, @starting_dt) SET @EndYr = DATEPART(yyyy, @ending_dt) DECLARE @StartYear AS INT = YEAR(@starting_dt); DECLARE @EndYear AS INT = YEAR(@ending_dt); WITH cteYears AS ( SELECT YYYY = @StartYear UNION ALL SELECT YYYY + 1 FROM cteYears WHERE YYYY < @EndYear ), cteMonths AS ( SELECT MM = 1 UNION ALL SELECT MM + 1 FROM cteMonths WHERE MM < 12 ), cteDays AS ( SELECT DD = 1 UNION ALL SELECT DD + 1 FROM cteDays WHERE DD < 31 ), cteDatesRaw AS ( SELECT YYYY = YYYY , MM = MM , DD = DD , DateKey = YYYY * 10000 + MM * 100 + DD , FullDate = CAST(CAST(YYYY * 10000 + MM * 100 + DD AS CHAR(8)) AS DATE) FROM cteYears CROSS JOIN cteMonths CROSS JOIN cteDays WHERE ISDATE(YYYY * 10000 + MM * 100 + DD) = 1 ) INSERT INTO [DimDate] ( [DateKey] , [FullDate] , [MonthNumberOfYear] , [MonthNumberOfQuarter] , [ISOYearAndWeekNumber] , [ISOWeekNumberOfYear] , [SSWeekNumberOfYear] , [ISOWeekNumberOfQuarter_454_Pattern] , [SSWeekNumberOfQuarter_454_Pattern] , [SSWeekNumberOfMonth] , [DayNumberOfYear] , [DaysSince1900] , [DayNumberOfFiscalYear] , [DayNumberOfQuarter] , [DayNumberOfMonth] , [DayNumberOfWeek_Sun_Start] , [MonthName] , [MonthNameAbbreviation] , [DayName] , [DayNameAbbreviation] , [CalendarYear] , [CalendarYearMonth] , [CalendarYearQtr] , [CalendarSemester] , [CalendarQuarter] , [FiscalYear] , [FiscalMonth] , [FiscalQuarter] , [FiscalYearMonth] , [FiscalYearQtr] , [QuarterNumber] , [YYYYMMDD] , [MM/DD/YYYY] , [YYYY/MM/DD] , [YYYY-MM-DD] , [MonDDYYYY] , [IsLastDayOfMonth] , [IsWeekday] , [IsWeekend] , [PeriodYearMonth] ) SELECT d.DateKey , d.FullDate , d.MM AS [MonthNumberOfYear] , DATEDIFF(MM, DATEADD(qq, DATEDIFF(qq, 0, d.FullDate), 0), d.FullDate) + 1 AS MonthNumberOfQuarter , CASE WHEN DATEPART(mm, d.FullDate) = 1 AND DATEPART(isoww, d.FullDate) > 50 THEN CAST(d.YYYY - 1 AS CHAR(4)) + ‘W‘ + RIGHT(‘0‘ + CAST(DATEPART(isoww, d.FullDate) AS VARCHAR(2)), 2) WHEN DATEPART(mm, d.FullDate) = 12 AND DATEPART(isoww, d.FullDate) < 40 THEN CAST(d.YYYY + 1 AS CHAR(4)) + ‘W‘ + RIGHT(‘0‘ + CAST(DATEPART(isoww, d.FullDate) AS VARCHAR(2)), 2) ELSE CAST(d.YYYY AS CHAR(4)) + ‘W‘ + RIGHT(‘0‘ + CAST(DATEPART(isoww, d.FullDate) AS VARCHAR(2)), 2) END AS ISOYearAndWeekNumber , DATEPART(isoww, d.FullDate) AS ISOWeekNumberOfYear , DATEPART(ww, d.FullDate) AS SSWeekNumberOfYear , CASE WHEN DATEPART(isoww, d.FullDate) < 14 THEN DATEPART(isoww, d.FullDate) WHEN DATEPART(isoww, d.FullDate) > 13 AND DATEPART(isoww, d.FullDate) < 27 THEN DATEPART(isoww, d.FullDate) - 13 WHEN DATEPART(isoww, d.FullDate) > 26 AND DATEPART(isoww, d.FullDate) < 40 THEN DATEPART(isoww, d.FullDate) - 26 ELSE DATEPART(isoww, d.FullDate) - 39 END AS ISOWeekNumberOfQuarter_454_Pattern , CASE WHEN DATEPART(wk, d.FullDate) < 14 THEN DATEPART(wk, d.FullDate) WHEN DATEPART(wk, d.FullDate) > 13 AND DATEPART(wk, d.FullDate) < 27 THEN DATEPART(wk, d.FullDate) - 13 WHEN DATEPART(wk, d.FullDate) > 26 AND DATEPART(wk, d.FullDate) < 40 THEN DATEPART(wk, d.FullDate) - 26 ELSE DATEPART(wk, d.FullDate) - 39 END AS SSWeekNumberOfQuarter_454_Pattern , DATEPART(wk, d.FullDate) - DATEPART(wk, DATEADD(MM, DATEDIFF(MM, 0, d.FullDate), 0)) + 1 AS SSWeekNumberOfMonth , DATEPART(dy, d.FullDate) AS DayNumberOfYear , DATEDIFF(dd, ‘18991231‘, d.FullDate) AS DaysSince1900 , CASE -- 0ffset < 0 and start of fy < current year WHEN YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) < d.YYYY AND @FiscalYearMonthsOffset < 0 THEN DATEPART(dy, d.FullDate) + DATEPART(dy, -- Last day of previous year CAST(CAST(YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS CHAR(4)) + ‘1231‘ AS DATETIME)) - DATEPART(dy, -- Start date of Fiscal year DATEADD(mm, 1, CAST(CAST(CAST(YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS CHAR(4)) + RIGHT(‘00‘ + CAST(@FiscalYearMonthsOffset * -1 AS VARCHAR(2)), 2) + ‘01‘ AS CHAR(8)) AS DATETIME)) - 1) -- 0ffset > 0 and start of fy < current year WHEN YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) - 1 < d.YYYY AND @FiscalYearMonthsOffset > 0 THEN DATEPART(dy, d.FullDate) + DATEPART(dy, -- Last day of previous year CAST(CAST(YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) - 1 AS CHAR(4)) + ‘1231‘ AS DATETIME)) - DATEPART(dy, -- Start date of Fiscal year CAST(CAST(CAST(YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) - 1 AS CHAR(4)) + RIGHT(‘00‘ + CAST(13 - @FiscalYearMonthsOffset AS VARCHAR(2)), 2) + ‘01‘ AS CHAR(8)) AS DATETIME) - 1) -- 0ffset < 0 and start of fy = current year WHEN YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) = d.YYYY AND @FiscalYearMonthsOffset < 0 THEN DATEPART(dy, d.FullDate) - DATEPART(dy, -- Start date of Fiscal year DATEADD(mm, 1, CAST(CAST(CAST(YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS CHAR(4)) + RIGHT(‘00‘ + CAST(@FiscalYearMonthsOffset * -1 AS VARCHAR(2)), 2) + ‘01‘ AS CHAR(8)) AS DATETIME)) - 1) -- 0ffset > 0 and start of fy = current year WHEN YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) - 1 = d.YYYY AND @FiscalYearMonthsOffset > 0 THEN DATEPART(dy, d.FullDate) - DATEPART(dy, -- Start date of Fiscal year CAST(CAST(CAST(YEAR(DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) - 1 AS CHAR(4)) + RIGHT(‘00‘ + CAST(13 - @FiscalYearMonthsOffset AS VARCHAR(2)), 2) + ‘01‘ AS CHAR(8)) AS DATETIME) - 1) ELSE DATEPART(dy, d.FullDate) END AS [DayNumberOfFiscalYear] , DATEDIFF(dd, DATEADD(qq, DATEDIFF(qq, 0, d.FullDate), 0), d.FullDate) + 1 AS DayNumberOfQuarter , d.DD AS DayNumberOfMonth , DATEPART(dw, d.FullDate) AS DayNumberOfWeek_Sun_Start , DATENAME(MONTH, d.FullDate) AS MonthName , LEFT(DATENAME(MONTH, d.FullDate), 3) AS MonthNameAbbreviation , DATENAME(dw, d.FullDate) AS DayName , LEFT(DATENAME(dw, d.FullDate), 3) AS DayNameAbbreviation , d.YYYY AS CalendarYear , CONVERT(VARCHAR(7), d.FullDate, 126) AS CalendarYearMonth , CAST(d.YYYY AS CHAR(4)) + ‘-‘ + RIGHT(‘0‘ + CAST(DATEPART(qq, d.FullDate) AS CHAR(1)), 2) AS CalendarYearQtr , CASE d.MM WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 1 WHEN 5 THEN 1 WHEN 6 THEN 1 ELSE 2 END AS CalendarSemester , DATEPART(qq, d.FullDate) AS CalendarQuarter , DATEPART(yyyy, DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS [FiscalYear] , DATEPART(mm, DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS [FiscalMonth] , DATEPART(qq, DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS [FiscalQuarter] , CAST(DATEPART(yyyy, DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS CHAR(4)) + ‘-‘ + RIGHT(‘0‘ + CAST(DATEPART(mm, DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS VARCHAR(2)), 2) AS [FiscalYearMonth] , CAST(DATEPART(yyyy, DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS CHAR(4)) + ‘Q‘ + RIGHT(‘0‘ + CAST(DATEPART(qq, DATEADD(mm, @FiscalYearMonthsOffset, d.FullDate)) AS VARCHAR(2)), 2) AS [FiscalYearQtr] , DATEDIFF(qq, ‘18991231‘, d.FullDate) AS [QuarterNumber] , CONVERT(VARCHAR(8), d.FullDate, 112) AS [YYYYMMDD] , CONVERT(VARCHAR(10), d.FullDate, 101) AS [MM/DD/YYYY] , CONVERT(VARCHAR(10), d.FullDate, 111) AS [YYYY/MM/DD] , REPLACE(CONVERT(VARCHAR(10), d.FullDate, 111), ‘/‘, ‘-‘) AS [YYYY-MM-DD] , LEFT(DATENAME(MONTH, d.FullDate), 3) + ‘ ‘ + RIGHT(‘0‘ + CAST(DATEPART(dd, d.FullDate) AS VARCHAR(2)), 2) + ‘ ‘ + CAST(d.YYYY AS CHAR(4)) AS [MonDDYYYY] , CASE WHEN d.FullDate = DATEADD(d, -DAY(DATEADD(mm, 1, d.FullDate)), DATEADD(mm, 1, d.FullDate)) THEN ‘Y‘ ELSE ‘N‘ END AS IsLastDayOfMonth , CASE DATEPART(dw, d.FullDate) WHEN 1 THEN ‘N‘ WHEN 7 THEN ‘N‘ ELSE ‘Y‘ END AS IsWeekday , CASE DATEPART(dw, d.FullDate) WHEN 1 THEN ‘Y‘ WHEN 7 THEN ‘Y‘ ELSE ‘N‘ END AS IsWeekend , CAST(d.YYYY AS CHAR(4)) + ‘年‘ + DATENAME(MONTH, d.FullDate) + ‘月‘ PeriodYearMonth FROM cteDatesRaw d WHERE d.DateKey BETWEEN CAST(CONVERT(CHAR(8), @starting_dt, 112) AS INT) AND CAST(CONVERT(CHAR(8), @ending_dt, 112) AS INT)GO
DimDate
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。