SQL Server日期维度
一、常见日期维度查询语句
--data当前日期 2022-04-02 select CONVERT(varchar(10),GETDATE(),120) --年份2022 SELECT CAST(YEAR(GETDATE()) AS int) --年季20222 SELECT YEAR(GETDATE())*10 + CEILING(MONTH(GETDATE())/3.0) --年月202204 SELECT YEAR(GETDATE())*100 + MONTH(GETDATE()) --年周202214 SELECT YEAR(GETDATE())*100 + DATEPART(WK,GETDATE()) --季度2 SELECT CEILING(MONTH(GETDATE())/3.0) --月4 SELECT MONTH(GETDATE()) --周14 SELECT DATEPART(WK,GETDATE()) --天2 SELECT DAY(GETDATE()) --年度最小日期2022-01-01 00:00:00.000 select dateadd(year, datediff(year, 0, GETDATE()), 0) --年度最大日期(思路：下年的第一天减去一天)2022-12-31 00:00:00.000 select dateadd(year, datediff(year, 0, dateadd(year, 1, GETDATE())), -1) --季度最小日期2022-04-01 00:00:00.000 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) --季度最大日期2022-06-30 00:00:00.000 SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)+CASE WHEN DATEPART(Quarter,GETDATE()) in(1,4)THEN'31'ELSE'30'END) --月度最小日期2022-04-01 00:00:00.000 select dateadd(month, datediff(month, 0, GETDATE()), 0) --月度最大日期(思路：下月的第一天减去一天)2022-04-30 00:00:00.000 select dateadd(month, datediff(month, 0, dateadd(month, 1, GETDATE())), -1) --周第一天2022-03-28 15:07:16.650 --设置周一为每周的第一天（sql server默认周日为每周第一天） SET DATEFIRST 1 SELECT dateadd(day,1-(datepart(weekday,GETDATE())),GETDATE()) --周最后一天(周的第一天加6)2022-04-03 15:07:16.650 SELECT DATEADD(day,1-(datepart(weekday,GETDATE()))+6,GETDATE())
二、建立日期维度表
CREATE TABLE [dbo].[d_time]( [id] [INT] NOT NULL, [date] [DATETIME] NOT NULL, [name] [NVARCHAR](11) NULL, [year] [INT] NOT NULL, [year_quarter] [INT] NOT NULL, [year_month] [INT] NOT NULL, [year_week] [INT] NOT NULL, [quarter] [INT] NOT NULL, [month] [INT] NOT NULL, [week] [INT] NOT NULL, [day] [INT] NOT NULL, [year_begin_date] [DATE] NOT NULL, [year_end_date] [DATE] NOT NULL, [quarter_begin_date] [DATE] NOT NULL, [quarter_end_date] [DATE] NOT NULL, [month_begin_date] [DATE] NOT NULL, [month_end_date] [DATE] NOT NULL, [week_begin_date] [DATE] NOT NULL, [week_end_date] [DATE] NOT NULL, CONSTRAINT [PK_d_time] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
三、维护日期维度存储过程
CREATE ALTER PROCEDURE [dbo].[sp_insert_time] @type INT --功能类型："0"增量，"1"全量 AS SET NOCOUNT ON; BEGIN DECLARE @data DATETIME; --当前日期 DECLARE @begin_time DATETIME; --起始时间 DECLARE @end_time DATETIME; --截止日期 --设置周一为每周的第一天 SET DATEFIRST 1; --获取当前日期(2021-11-11 00:00:00.000) SET @data = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME); --设置截止时间（当天日期向后追加一年） SET @end_time = DATEADD(yy, 1, @data); --判断功能类型-增量 IF @type = 0 BEGIN --日期增量(如果表中最大日期小于明年今日) IF(SELECT MAX(date) FROM dbo.d_time) < @end_time BEGIN --设置起始时间(表中存在最大日期+1天) SET @begin_time = DATEADD(DAY,1,(SELECT MAX(date) FROM dbo.d_time)); --插入开始 WHILE @begin_time <= @end_time BEGIN SET @data = @begin_time; INSERT INTO [dbo].[d_time] ( [id], [date], [name], [year], [year_quarter], [year_month], [year_week], [quarter], [month], [week], [day], [year_begin_date], [year_end_date], [quarter_begin_date], [quarter_end_date], [month_begin_date], [month_end_date], [week_begin_date], [week_end_date] ) VALUES (cast(CONVERT(varchar(12),@data,112) AS INT ) ,CONVERT(varchar(10),@data,120) ,CAST(YEAR(@data) AS VARCHAR)+'年'+RIGHT((100+ MONTH(@data)),2)+'月'+RIGHT(100+DAY(@data),2)+'日' ,CAST(YEAR(@data) AS int) ,YEAR(@data)*10 + CEILING(MONTH(@data)/3.0) ,YEAR(@data)*100 + MONTH(@data) ,YEAR(@data)*100 + DATEPART(WK,@data) ,CEILING(MONTH(@data)/3.0) ,MONTH(@data) ,DATEPART(WK,@data) ,DAY(@data) ,dateadd(year, datediff(year, 0, @data), 0) ,dateadd(year, datediff(year, 0, dateadd(year, 1, @data)), -1) ,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) ,CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@data)*3-Month(@data),@data),120)+CASE WHEN DATEPART(Quarter,@data) in(1,4)THEN'31'ELSE'30'END) ,dateadd(month, datediff(month, 0, @data), 0) ,dateadd(month, datediff(month, 0, dateadd(month, 1, @data)), -1) ,dateadd(day,1-(datepart(weekday,@data)),@data) ,DATEADD(day,1-(datepart(weekday,@data))+6,@data)); SET @begin_time = DATEADD(DAY, 1, @begin_time); END; END; END; ----判断功能类型-全量 IF @type = 1 BEGIN --全量前清空日期维度表 TRUNCATE TABLE [dbo].[d_time] --先插入一条null数据 BEGIN SET @data ='1900-01-01 00:00:00.000'; INSERT INTO [dbo].[d_time] ( [id], [date], [name], [year], [year_quarter], [year_month], [year_week], [quarter], [month], [week], [day], [year_begin_date], [year_end_date], [quarter_begin_date], [quarter_end_date], [month_begin_date], [month_end_date], [week_begin_date], [week_end_date] ) VALUES (cast(CONVERT(varchar(12),@data,112) AS INT ) ,CONVERT(varchar(10),@data,120) ,CAST(YEAR(@data) AS VARCHAR)+'年'+RIGHT((100+ MONTH(@data)),2)+'月'+RIGHT(100+DAY(@data),2)+'日' ,CAST(YEAR(@data) AS int) ,YEAR(@data)*10 + CEILING(MONTH(@data)/3.0) ,YEAR(@data)*100 + MONTH(@data) ,YEAR(@data)*100 + DATEPART(WK,@data) ,CEILING(MONTH(@data)/3.0) ,MONTH(@data) ,DATEPART(WK,@data) ,DAY(@data) ,dateadd(year, datediff(year, 0, @data), 0) ,dateadd(year, datediff(year, 0, dateadd(year, 1, @data)), -1) ,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) ,CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@data)*3-Month(@data),@data),120)+CASE WHEN DATEPART(Quarter,@data) in(1,4)THEN'31'ELSE'30'END) ,dateadd(month, datediff(month, 0, @data), 0) ,dateadd(month, datediff(month, 0, dateadd(month, 1, @data)), -1) ,dateadd(day,1-(datepart(weekday,@data)),@data) ,DATEADD(day,1-(datepart(weekday,@data))+6,@data)); END --正式数据插入开始 SET @begin_time = '2018-01-01 00:00:00.000'; WHILE @begin_time < DATEADD(DAY, 1, @end_time) BEGIN SET @data = @begin_time; --插入开始 INSERT INTO [dbo].[d_time] ( [id], [date], [name], [year], [year_quarter], [year_month], [year_week], [quarter], [month], [week], [day], [year_begin_date], [year_end_date], [quarter_begin_date], [quarter_end_date], [month_begin_date], [month_end_date], [week_begin_date], [week_end_date] ) VALUES (cast(CONVERT(varchar(12),@data,112) AS INT ) ,CONVERT(varchar(10),@data,120) ,CAST(YEAR(@data) AS VARCHAR)+'年'+RIGHT((100+ MONTH(@data)),2)+'月'+RIGHT(100+DAY(@data),2)+'日' ,CAST(YEAR(@data) AS int) ,YEAR(@data)*10 + CEILING(MONTH(@data)/3.0) ,YEAR(@data)*100 + MONTH(@data) ,YEAR(@data)*100 + DATEPART(WK,@data) ,CEILING(MONTH(@data)/3.0) ,MONTH(@data) ,DATEPART(WK,@data) ,DAY(@data) ,dateadd(year, datediff(year, 0, @data), 0) ,dateadd(year, datediff(year, 0, dateadd(year, 1, @data)), -1) ,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) ,CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@data)*3-Month(@data),@data),120)+CASE WHEN DATEPART(Quarter,@data) in(1,4)THEN'31'ELSE'30'END) ,dateadd(month, datediff(month, 0, @data), 0) ,dateadd(month, datediff(month, 0, dateadd(month, 1, @data)), -1) ,dateadd(day,1-(datepart(weekday,@data)),@data) ,DATEADD(day,1-(datepart(weekday,@data))+6,@data)); SET @begin_time = DATEADD(DAY, 1, @begin_time); END; END; END;
四、日期格式转换
使用Convert()函数：
select convert(char(10),GetDate(),120) as Date
* 第3个参数就是用来设置日期类型数据的显示样式的，下面介绍几种样式的参数：
100 mm dd yyyy
101 mm/dd/yyyy
102 yyyy.mm.dd
103 dd/mm/yyyy
106 dd mm yyyy
108 hh:mi:ss(时间)
111 yyyy/mm/dd
112 yyyymmdd
120 yyyy-mm-dd
SELECT CONVERT(varchar(100), GETDATE(), 0) 05 9 2011 9:12AM SELECT CONVERT(varchar(100), GETDATE(), 1) 05/09/11 SELECT CONVERT(varchar(100), GETDATE(), 2) 11.05.09 SELECT CONVERT(varchar(100), GETDATE(), 3) 09/05/11 SELECT CONVERT(varchar(100), GETDATE(), 4) 09.05.11 SELECT CONVERT(varchar(100), GETDATE(), 5) 09-05-11 SELECT CONVERT(varchar(100), GETDATE(), 6) 09 05 11 SELECT CONVERT(varchar(100), GETDATE(), 7) 05 09, 11 SELECT CONVERT(varchar(100), GETDATE(), 8) 09:13:14 SELECT CONVERT(varchar(100), GETDATE(), 9) 05 9 2011 9:13:14:670AM SELECT CONVERT(varchar(100), GETDATE(), 10) 05-09-11 SELECT CONVERT(varchar(100), GETDATE(), 11) 11/05/09 SELECT CONVERT(varchar(100), GETDATE(), 12) 110509 SELECT CONVERT(varchar(100), GETDATE(), 13) 09 05 2011 09:13:14:670 SELECT CONVERT(varchar(100), GETDATE(), 14) 09:13:14:670 SELECT CONVERT(varchar(100), GETDATE(), 20) 2011-05-09 09:13:14 SELECT CONVERT(varchar(100), GETDATE(), 21) 2011-05-09 09:13:14.670 SELECT CONVERT(varchar(100), GETDATE(), 22) 05/09/11 9:15:33 AM SELECT CONVERT(varchar(100), GETDATE(), 23) 2011-05-09 SELECT CONVERT(varchar(100), GETDATE(), 24) 09:15:33 SELECT CONVERT(varchar(100), GETDATE(), 25) 2011-05-09 09:15:33.140 SELECT CONVERT(varchar(100), GETDATE(), 100) 05 9 2011 9:15AM SELECT CONVERT(varchar(100), GETDATE(), 101) 05/09/2011 SELECT CONVERT(varchar(100), GETDATE(), 102) 2011.05.09 SELECT CONVERT(varchar(100), GETDATE(), 103) 09/05/2011 SELECT CONVERT(varchar(100), GETDATE(), 104) 09.05.2011 SELECT CONVERT(varchar(100), GETDATE(), 105) 09-05-2011 SELECT CONVERT(varchar(100), GETDATE(), 106) 09 05 2011 SELECT CONVERT(varchar(100), GETDATE(), 107) 05 09, 2011 SELECT CONVERT(varchar(100), GETDATE(), 108) 09:16:38 SELECT CONVERT(varchar(100), GETDATE(), 109) 05 9 2011 9:16:38:543AM SELECT CONVERT(varchar(100), GETDATE(), 110) 05-09-2011 SELECT CONVERT(varchar(100), GETDATE(), 111) 2011/05/09 SELECT CONVERT(varchar(100), GETDATE(), 112) 20110509 SELECT CONVERT(varchar(100), GETDATE(), 113) 09 05 2011 09:17:19:857 SELECT CONVERT(varchar(100), GETDATE(), 114) 09:17:19:857 SELECT CONVERT(varchar(100), GETDATE(), 120) 2011-05-09 09:17:19 SELECT CONVERT(varchar(100), GETDATE(), 121) 2011-05-09 09:17:19.857 SELECT CONVERT(varchar(100), GETDATE(), 126) 2011-05-09T09:17:19.857 SELECT CONVERT(varchar(100), GETDATE(), 130) 6 ????? ??????? 1432 9:17:19:857AM SELECT CONVERT(varchar(100), GETDATE(), 131) 6/06/1432 9:17:19:857AM
