--新建
CREATE TABLE [dbo].[BI_Dim_Date](
--YYMMDD
[DateF] [nvarchar](20) NULL, --法国日期格式
[DateKey] [nvarchar](10) NULL,
[Dim_Year] [int] NULL,
[Dim_Month] [int] NULL,
[Dim_Day] [int] NULL,
--季度
[Qu] [int] NULL,
[QuCN] [varchar](20) NULL,
[QuEN] [varchar](20) NULL,
--月份
[MonthCN] [varchar](20) NULL,
[MonthEN] [varchar](20) NULL,
--旬 1 上旬 2 中旬 3 下旬
[Ten] [int] NULL,
[TenCN] [varchar](20) NULL,
--周
[Dim_Week] [int] NULL,
--星期几
[WeekDayCN] [varchar](20) NULL,
[WeekDayEN] [varchar](20) NULL,
--yy-mm-dd
[Dim_Date1] [date] NULL,
--DD/MM/YY
[Dim_Date2] [nvarchar](10) NULL,
---是否节假日 1放假 0正常
[IsDayOff] [int] NULL,
--假日说明
[Event_Name] [varchar](20) NULL
)
---插入顺序很建表顺序要对应
DECLARE @BeginDate DATE;
SELECT @BeginDate = '20131230';
WHILE @BeginDate<='20171231'
BEGIN
INSERT INTO BI_Dim_Date
SELECT
dbo.ChangeIt(@BeginDate) as DateF,
--CONVERT(varchar(10),@BeginDate,120) AS DateKey --查出格式2013-12-30
CONVERT(varchar(10),@BeginDate,112) AS DateKey,--
YEAR(@BeginDate) AS Dim_Year,
MONTH(@BeginDate) as Dim_Month,
Day(@BeginDate) AS Dim_Day,
Datepart(QUARTER,@BeginDate) AS Qu,
CASE
WHEN Datepart(QUARTER,@BeginDate)=1 then '第一季度'
WHEN Datepart(QUARTER,@BeginDate)=2 then '第二季度'
WHEN Datepart(QUARTER,@BeginDate)=3 then '第三季度'
ELSE '第四季度'
END AS QuCN,
CASE
WHEN Datepart(QUARTER,@BeginDate)=1 then 'Q1'
WHEN Datepart(QUARTER,@BeginDate)=2 then 'Q2'
WHEN Datepart(QUARTER,@BeginDate)=3 then 'Q3'
ELSE 'Q4'
END AS QuEN,
case
when MONTH(@BeginDate)= 1 then '一月'
when MONTH(@BeginDate)= 2 then '二月'
when MONTH(@BeginDate)= 3 then '三月'
when MONTH(@BeginDate)= 4 then '四月'
when MONTH(@BeginDate)= 5 then '五月'
when MONTH(@BeginDate)= 6 then '六月'
when MONTH(@BeginDate)= 7 then '七月'
when MONTH(@BeginDate)= 8 then '八月'
when MONTH(@BeginDate)= 9 then '九月'
when MONTH(@BeginDate)= 10 then '十月'
when MONTH(@BeginDate)= 11 then '十一月'
else '十二月'
end as MonthCN,
case
when MONTH(@BeginDate)= 1 then 'Jan'
when MONTH(@BeginDate)= 2 then 'Feb'
when MONTH(@BeginDate)= 3 then 'Mar'
when MONTH(@BeginDate)= 4 then 'Apr'
when MONTH(@BeginDate)= 5 then 'May'
when MONTH(@BeginDate)= 6 then 'Jun'
when MONTH(@BeginDate)= 7 then 'Jul'
when MONTH(@BeginDate)= 8 then 'Aug'
when MONTH(@BeginDate)= 9 then 'Sept'
when MONTH(@BeginDate)= 10 then 'Oct'
when MONTH(@BeginDate)= 11 then 'Nov'
else 'Dec'
end as MonthEN,
CASE
when DATEPART(DAY,@BeginDate)<=10 THEN 1
WHEN DATEPART(DAY,@BeginDate)>
20 THEN 3
ELSE 2
END as Ten,
CASE
when DATEPART(DAY,@BeginDate)<=10 THEN '上旬'
WHEN DATEPART(DAY,@BeginDate)>20 THEN '下旬'
ELSE '中旬'
END as TenCN,
DATEPART(WEEK,@BeginDate)-1 AS Dim_Week,
DATENAME(WEEKDAY,@BeginDate) as WeekDayCN,
case
when DATENAME(WEEKDAY,@BeginDate)= '星期一' then 'Mon'
when DATENAME(WEEKDAY,@BeginDate)= '星期二' then 'Tue'
when DATENAME(WEEKDAY,@BeginDate)= '星期三' then 'Wed'
when DATENAME(WEEKDAY,@BeginDate)= '星期四' then 'Thu'
when DATENAME(WEEKDAY,@BeginDate)= '星期五' then 'Fri'
when DATENAME(WEEKDAY,@BeginDate)= '星期六' then 'Sat'
else 'Sun'
end as WeekDayEN,
CONVERT (varchar,@BeginDate,112) as Dim_Date1,
CONVERT(varchar(100), cast(@BeginDate as datetime), 101) as Dim_Date2,
0 as IsDayOff,
'' as Event_Name
SET @BeginDate=DATEADD(DAY,1,@BeginDate);
end;
select * from BI_Dim_Date
--TRUNCATE TABLE BI_Dim_Date
--drop table BI_Dim_Date
--select GETDATE()--2014-11-24 12:48:47
--Select CONVERT(varchar(100), '20140101', 101) --20140101
--select convert(varchar(10),convert(varchar(10),'20140101',120),112)--20140101
--select convert(varchar(10),'20140101',120) --20140101
-- Select CONVERT(varchar(100), GETDATE(), 101) 11/24/2014
--select DATENAME(WEEKDAY,'20131230') as WeekDay
--set language N'English' --英文格式的星期几
--select DATENAME(WEEKDAY,'20131230') as WeekDay
--set language N'简体中文'
--select DATENAME(WEEKDAY,'20131230') as WeekDay
--Select CONVERT(varchar(100), '20140101', 101) --20140101
--Select CONVERT(varchar(100), GETDATE(), 101) --11/24/2014
-- Select CONVERT(varchar(100), cast('20140101' as datetime), 101) --01/01/2014
--sp_rename 'Dim_Date.Month','Dim_Month','column'
--sp_rename 'Dim_Date.Day','Dim_Day','column'
select * from BI_Dim_Date where DateKey>='20161229'
update BI_Dim_Date set Dim_Week=52 where DateKey between '20131230' and '20140105'
update BI_Dim_Date set Dim_Week=52 where DateKey between '20141229' and '20150104'
update BI_Dim_Date set Dim_Week=52 where DateKey between '20151228' and '20160103'
update BI_Dim_Date set Dim_Week=52 where DateKey='20170101'
update BI_Dim_Date set Dim_Week=Dim_Week-1 where WeekDayEN='Sun'
update BI_Dim_Date set Dim_Week=Dim_Week+1 where DateKey>='20170101'
--select SUBSTRING('20131230',3,2)
--select SUBSTRING('20131230',7,2)
--select
--case
-- when SUBSTRING('20131230',5,2)= 01 then 'Jan'
--when SUBSTRING('20131230',5,2)= 02 then 'Feb'
---when SUBSTRING('20131230',5,2)= 03 then 'Mar'
--when SUBSTRING('20131230',5,2)= 04 th