[转载]建立完善的日期定义表

[转载]建立完善的日期定义表 – vvian – 博客园.

随着公司业务给规模越来越大，各级领导和销售部门、业务主管对各类业务数据报表的需求也越来越多，越来越复杂，针对这种情况，为了更好的处理好各类数据的简单分析，我建立了一张较为完善的日期定义表，现将过程与大家共同分享，还请大家提出不足和意见。

在这张表中，我将每一个日期划分出不同的年度、季度、月度、星期等内容，这样在对业务数据进行统计时，只要业务日期匹配，再来选择相应的统计时段就 可以很快的得到数据，而不需要在SQL语句中再进行year/month/day/quarter等计算，加快了运算速度。如果以此表为主表，与其他表进 行外接连时，还可以直观的显示出无销售收入的日期，避免日期跳行。同时，我还在表中加入了农历日期，方便进行传统农历时段的销售统计（春节、中秋等），而 且在开发前端程序的时候，可以直接引用表中的农历日期，不需要再找其他的算法。

首先，建一张基础数据表，内容如下：

CREATE TABLE [dbo].[date_def](
    [count_id] [int] IDENTITY(1,1) NOT NULL,
    [count_date] [datetime] NOT NULL,
    [count_year] [smallint] NULL,
    [count_quarter] [tinyint] NULL,
    [count_month] [tinyint] NULL,
    [count_week] [tinyint] NULL,
    [count_weekday] [tinyint] NULL,
    [count_day] [smallint] NULL,
    [chinese_year] [varchar](10) NULL,
    [lunar_year] [varchar](10) NULL,
    [lunar_date] [varchar](10) NULL,
    [work_day] [bit] NULL,
 CONSTRAINT [PK_date_def] PRIMARY KEY CLUSTERED
(
    [count_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]

CREATE UNIQUE INDEX Idx_date_def ON date_def(count_date)

接下来，在基础数据表中插入我们需要的日期值，我们先插入从1999年到2012年的日期数据。

declare @startdate datetime,@enddate datetime
set @startdate='1999-01-01'
set @enddate='2003-12-31'

insert into date_def (count_date)
select convert(varchar(10),dateadd(day,number,@startdate),120)
from
    master..spt_values
where
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>=0
    and type='p'

大家可能会发现一个问题，我上次说的是插入的是从1999年到2012年的数据，但代码只是从1999到2003年，实际上，我们插入日期的时候， 利用了master..spt_values表中的顺序数，但这个表中只有2048个顺序号，因此我们只能每5年一个单位的进行插入，从1999到 2003，然后2004到2008，最后2009到2012，完成插入的过程。

下面这一步就很简单了，将每一个日期值的年度、季度、月份、星期、星期几、第几天这些信息分离出来。此处要注意的一定是，西方人习惯于星期天做为一 周的第一天，而中国更习惯以星期一来做为一周的开始，因此就需要调整一个参数。如果你不需要调整这个参数的话，就要注意下面的工作日的设定了。

-- 设定星期一为每周的第一天，默认为星期天为每周第一天
SET   DATEFIRST   1

update date_def
set count_year = year(count_date),count_quarter = datepart(qq,count_date),
count_month = month(count_date),count_week = datepart(week,count_date),
count_day = datepart(dy,count_date),count_weekday = datepart(weekday,count_date)
-- 默认除周六周日外都是工作日，如你不需要修改每周第一天，此处就要修改
update date_def
set work_day = case when count_weekday between 6 and 7 then 0 else 1 end

实际上这只是一个很简单的工作日设定，每年底还要根据国务院发布的次年休假时间表来确认第二年的工作日情况，
有个默认值也是为了方便其他不需要修改的地方。点击下载1999年到2012年的真实工作日的数据。

下一步就是填充三个农历字段的内容了，一些代码是从网上找到少许修改的，在此要谢谢提供代码的各位前辈了。

先建立一张农历的基础数据表：

 CREATE TABLE SolarData (
  yearid decimal(16, 0),
  data char(7),
  dataint decimal(16, 0));
INSERT INTO SolarData VALUES (1900, '0x04bd8', 19416);
INSERT INTO SolarData VALUES (1901, '0x04ae0', 19168);
INSERT INTO SolarData VALUES (1902, '0x0a570', 42352);
INSERT INTO SolarData VALUES (1903, '0x054d5', 21717);
INSERT INTO SolarData VALUES (1904, '0x0d260', 53856);
INSERT INTO SolarData VALUES (1905, '0x0d950', 55632);
INSERT INTO SolarData VALUES (1906, '0x16554', 91476);
INSERT INTO SolarData VALUES (1907, '0x056a0', 22176);
INSERT INTO SolarData VALUES (1908, '0x09ad0', 39632);
INSERT INTO SolarData VALUES (1909, '0x055d2', 21970);
INSERT INTO SolarData VALUES (1910, '0x04ae0', 19168);
INSERT INTO SolarData VALUES (1911, '0x0a5b6', 42422);
INSERT INTO SolarData VALUES (1912, '0x0a4d0', 42192);
INSERT INTO SolarData VALUES (1913, '0x0d250', 53840);
INSERT INTO SolarData VALUES (1914, '0x1d255', 119381);
INSERT INTO SolarData VALUES (1915, '0x0b540', 46400);
INSERT INTO SolarData VALUES (1916, '0x0d6a0', 54944);
INSERT INTO SolarData VALUES (1917, '0x0ada2', 44450);
INSERT INTO SolarData VALUES (1918, '0x095b0', 38320);
INSERT INTO SolarData VALUES (1919, '0x14977', 84343);
INSERT INTO SolarData VALUES (1920, '0x04970', 18800);
INSERT INTO SolarData VALUES (1921, '0x0a4b0', 42160);
INSERT INTO SolarData VALUES (1922, '0x0b4b5', 46261);
INSERT INTO SolarData VALUES (1923, '0x06a50', 27216);
INSERT INTO SolarData VALUES (1924, '0x06d40', 27968);
INSERT INTO SolarData VALUES (1925, '0x1ab54', 109396);
INSERT INTO SolarData VALUES (1926, '0x02b60', 11104);
INSERT INTO SolarData VALUES (1927, '0x09570', 38256);
INSERT INTO SolarData VALUES (1928, '0x052f2', 21234);
INSERT INTO SolarData VALUES (1929, '0x04970', 18800);
INSERT INTO SolarData VALUES (1930, '0x06566', 25958);
INSERT INTO SolarData VALUES (1931, '0x0d4a0', 54432);
INSERT INTO SolarData VALUES (1932, '0x0ea50', 59984);
INSERT INTO SolarData VALUES (1933, '0x06e95', 28309);
INSERT INTO SolarData VALUES (1934, '0x05ad0', 23248);
INSERT INTO SolarData VALUES (1935, '0x02b60', 11104);
INSERT INTO SolarData VALUES (1936, '0x186e3', 100067);
INSERT INTO SolarData VALUES (1937, '0x092e0', 37600);
INSERT INTO SolarData VALUES (1938, '0x1c8d7', 116951);
INSERT INTO SolarData VALUES (1939, '0x0c950', 51536);
INSERT INTO SolarData VALUES (1940, '0x0d4a0', 54432);
INSERT INTO SolarData VALUES (1941, '0x1d8a6', 120998);
INSERT INTO SolarData VALUES (1942, '0x0b550', 46416);
INSERT INTO SolarData VALUES (1943, '0x056a0', 22176);
INSERT INTO SolarData VALUES (1944, '0x1a5b4', 107956);
INSERT INTO SolarData VALUES (1945, '0x025d0', 9680);
INSERT INTO SolarData VALUES (1946, '0x092d0', 37584);
INSERT INTO SolarData VALUES (1947, '0x0d2b2', 53938);
INSERT INTO SolarData VALUES (1948, '0x0a950', 43344);
INSERT INTO SolarData VALUES (1949, '0x0b557', 46423);
INSERT INTO SolarData VALUES (1950, '0x06ca0', 27808);
INSERT INTO SolarData VALUES (1951, '0x0b550', 46416);
INSERT INTO SolarData VALUES (1952, '0x15355', 86869);
INSERT INTO SolarData VALUES (1953, '0x04da0', 19872);
INSERT INTO SolarData VALUES (1954, '0x0a5d0', 42448);
INSERT INTO SolarData VALUES (1955, '0x14573', 83315);
INSERT INTO SolarData VALUES (1956, '0x052d0', 21200);
INSERT INTO SolarData VALUES (1957, '0x0a9a8', 43432);
INSERT INTO SolarData VALUES (1958, '0x0e950', 59728);
INSERT INTO SolarData VALUES (1959, '0x06aa0', 27296);
INSERT INTO SolarData VALUES (1960, '0x0aea6', 44710);
INSERT INTO SolarData VALUES (1961, '0x0ab50', 43856);
INSERT INTO SolarData VALUES (1962, '0x04b60', 19296);
INSERT INTO SolarData VALUES (1963, '0x0aae4', 43748);
INSERT INTO SolarData VALUES (1964, '0x0a570', 42352);
INSERT INTO SolarData VALUES (1965, '0x05260', 21088);
INSERT INTO SolarData VALUES (1966, '0x0f263', 62051);
INSERT INTO SolarData VALUES (1967, '0x0d950', 55632);
INSERT INTO SolarData VALUES (1968, '0x05b57', 23383);
INSERT INTO SolarData VALUES (1969, '0x056a0', 22176);
INSERT INTO SolarData VALUES (1970, '0x096d0', 38608);
INSERT INTO SolarData VALUES (1971, '0x04dd5', 19925);
INSERT INTO SolarData VALUES (1972, '0x04ad0', 19152);
INSERT INTO SolarData VALUES (1973, '0x0a4d0', 42192);
INSERT INTO SolarData VALUES (1974, '0x0d4d4', 54484);
INSERT INTO SolarData VALUES (1975, '0x0d250', 53840);
INSERT INTO SolarData VALUES (1976, '0x0d558', 54616);
INSERT INTO SolarData VALUES (1977, '0x0b540', 46400);
INSERT INTO SolarData VALUES (1978, '0x0b5a0', 46496);
INSERT INTO SolarData VALUES (1979, '0x195a6', 103846);
INSERT INTO SolarData VALUES (1980, '0x095b0', 38320);
INSERT INTO SolarData VALUES (1981, '0x049b0', 18864);
INSERT INTO SolarData VALUES (1982, '0x0a974', 43380);
INSERT INTO SolarData VALUES (1983, '0x0a4b0', 42160);
INSERT INTO SolarData VALUES (1984, '0x0b27a', 45690);
INSERT INTO SolarData VALUES (1985, '0x06a50', 27216);
INSERT INTO SolarData VALUES (1986, '0x06d40', 27968);
INSERT INTO SolarData VALUES (1987, '0x0af46', 44870);
INSERT INTO SolarData VALUES (1988, '0x0ab60', 43872);
INSERT INTO SolarData VALUES (1989, '0x09570', 38256);
INSERT INTO SolarData VALUES (1990, '0x04af5', 19189);
INSERT INTO SolarData VALUES (1991, '0x04970', 18800);
INSERT INTO SolarData VALUES (1992, '0x064b0', 25776);
INSERT INTO SolarData VALUES (1993, '0x074a3', 29859);
INSERT INTO SolarData VALUES (1994, '0x0ea50', 59984);
INSERT INTO SolarData VALUES (1995, '0x06b58', 27480);
INSERT INTO SolarData VALUES (1996, '0x055c0', 21952);
INSERT INTO SolarData VALUES (1997, '0x0ab60', 43872);
INSERT INTO SolarData VALUES (1998, '0x096d5', 38613);
INSERT INTO SolarData VALUES (1999, '0x092e0', 37600);
INSERT INTO SolarData VALUES (2000, '0x0c960', 51552);
INSERT INTO SolarData VALUES (2001, '0x0d954', 55636);
INSERT INTO SolarData VALUES (2002, '0x0d4a0', 54432);
INSERT INTO SolarData VALUES (2003, '0x0da50', 55888);
INSERT INTO SolarData VALUES (2004, '0x07552', 30034);
INSERT INTO SolarData VALUES (2005, '0x056a0', 22176);
INSERT INTO SolarData VALUES (2006, '0x0abb7', 43959);
INSERT INTO SolarData VALUES (2007, '0x025d0', 9680);
INSERT INTO SolarData VALUES (2008, '0x092d0', 37584);
INSERT INTO SolarData VALUES (2009, '0x0cab5', 51893);
INSERT INTO SolarData VALUES (2010, '0x0a950', 43344);
INSERT INTO SolarData VALUES (2011, '0x0b4a0', 46240);
INSERT INTO SolarData VALUES (2012, '0x0baa4', 47780);
INSERT INTO SolarData VALUES (2013, '0x0ad50', 44368);
INSERT INTO SolarData VALUES (2014, '0x055d9', 21977);
INSERT INTO SolarData VALUES (2015, '0x04ba0', 19360);
INSERT INTO SolarData VALUES (2016, '0x0a5b0', 42416);
INSERT INTO SolarData VALUES (2017, '0x15176', 86390);
INSERT INTO SolarData VALUES (2018, '0x052b0', 21168);
INSERT INTO SolarData VALUES (2019, '0x0a930', 43312);
INSERT INTO SolarData VALUES (2020, '0x07954', 31060);
INSERT INTO SolarData VALUES (2021, '0x06aa0', 27296);
INSERT INTO SolarData VALUES (2022, '0x0ad50', 44368);
INSERT INTO SolarData VALUES (2023, '0x05b52', 23378);
INSERT INTO SolarData VALUES (2024, '0x04b60', 19296);
INSERT INTO SolarData VALUES (2025, '0x0a6e6', 42726);
INSERT INTO SolarData VALUES (2026, '0x0a4e0', 42208);
INSERT INTO SolarData VALUES (2027, '0x0d260', 53856);
INSERT INTO SolarData VALUES (2028, '0x0ea65', 60005);
INSERT INTO SolarData VALUES (2029, '0x0d530', 54576);
INSERT INTO SolarData VALUES (2030, '0x05aa0', 23200);
INSERT INTO SolarData VALUES (2031, '0x076a3', 30371);
INSERT INTO SolarData VALUES (2032, '0x096d0', 38608);
INSERT INTO SolarData VALUES (2033, '0x04bd7', 19415);
INSERT INTO SolarData VALUES (2034, '0x04ad0', 19152);
INSERT INTO SolarData VALUES (2035, '0x0a4d0', 42192);
INSERT INTO SolarData VALUES (2036, '0x1d0b6', 118966);
INSERT INTO SolarData VALUES (2037, '0x0d250', 53840);
INSERT INTO SolarData VALUES (2038, '0x0d520', 54560);
INSERT INTO SolarData VALUES (2039, '0x0dd45', 56645);
INSERT INTO SolarData VALUES (2040, '0x0b5a0', 46496);
INSERT INTO SolarData VALUES (2041, '0x056d0', 22224);
INSERT INTO SolarData VALUES (2042, '0x055b2', 21938);
INSERT INTO SolarData VALUES (2043, '0x049b0', 18864);
INSERT INTO SolarData VALUES (2044, '0x0a577', 42359);
INSERT INTO SolarData VALUES (2045, '0x0a4b0', 42160);
INSERT INTO SolarData VALUES (2046, '0x0aa50', 43600);
INSERT INTO SolarData VALUES (2047, '0x1b255', 111189);
INSERT INTO SolarData VALUES (2048, '0x06d20', 27936);
INSERT INTO SolarData VALUES (2049, '0x0ada0', 44448);

然后加入一个函数，农历日期就用这个函数来求出，对于原始的代码，我修改了一个地方，把“十一月”、“十二月”分别改成了“冬月”和“腊月”，对齐一点，好看，哈哈。
-- 功能：计算阳历1900/01/31 - 2050/01/22间某一天对应的阴历是多少
-- 算法：在一张表中用10进制格式保存某个农历年每月大小,有无闰月,闰月大小信息
-- 1.用12个2进制位来表示某个农历年每月的大小，大月记为1，否则为0
-- 2.用低4位来表示闰月的月份，没有闰月记为0
-- 3.用一个高位表示闰月的大小，闰月大记为0，闰月小或无闰月记为0
-- 4.再将该2进制数转化为10进制，存入表中
-- 农历2000年: 0 110010010110 0000 -> 0x0c960 -> 51552
-- 农历2001年: 0 110110010101 0100 -> 0x0d954 -> 55636
-- 采用查表的方式计算出农历日期
-- 作者：Angel_XJW

create function dbo.fn_cnyear(@solarday datetime)
returns nvarchar(30)
as
begin
  declare @soldata int
  declare @offset int
  declare @ilunar int
  declare @i int
  declare @j int
  declare @ydays int
  declare @mdays int
  declare @mleap int
  declare @mleap1 int
  declare @mleapnum int
  declare @bleap smallint
  declare @temp int
  declare @year nvarchar(10)
  declare @month nvarchar(10)
  declare @day nvarchar(10)
  declare @chinesenum nvarchar(10)
  declare @outputdate nvarchar(30)
  set @offset=datediff(day,'1900-01-30',@solarday)
  --确定农历年开始
  set @i=1900
  --set @offset=@soldata
  while @i<2050 and @offset>0
  begin
    set @ydays=348
    set @mleapnum=0
    select @ilunar=dataint from solardata where yearid=@i      

    --传回农历年的总天数
    set @j=32768
    while @j>8
    begin
      if @ilunar & @j >0
        set @ydays=@ydays+1
      set @j=@j/2
    end
    --传回农历年闰哪个月 1-12 , 没闰传回 0
    set @mleap = @ilunar & 15
    --传回农历年闰月的天数 ,加在年的总天数上
    if @mleap > 0
    begin
      if @ilunar & 65536 > 0
        set @mleapnum=30
      else
        set @mleapnum=29
      set @ydays=@ydays+@mleapnum
    end
    set @offset=@offset-@ydays
    set @i=@i+1
  end
  if @offset <= 0
  begin
    set @offset=@offset+@ydays
    set @i=@i-1
  end
  --确定农历年结束
  set @year=@i
  --确定农历月开始
  set @i = 1
  select @ilunar=dataint from solardata where yearid=@year
  --判断那个月是润月
  set @mleap = @ilunar & 15
  set @bleap = 0
  while @i < 13 and @offset > 0
  begin
    --判断润月
    set @mdays=0
    if (@mleap > 0 and @i = (@mleap+1) and @bleap=0)
    begin--是润月
      set @i=@i-1
      set @bleap=1
      set @mleap1= @mleap
      --传回农历年闰月的天数
      if @ilunar & 65536 > 0
        set @mdays = 30
      else
        set @mdays = 29
    end
    else
    --不是润月
    begin
      set @j=1
      set @temp = 65536
      while @j<=@i
      begin
        set @temp=@temp/2
        set @j=@j+1
      end      

      if @ilunar & @temp > 0
        set @mdays = 30
      else
        set @mdays = 29
    end      

    --解除润月
    if @bleap=1 and @i= (@mleap+1)
      set @bleap=0    

    set @offset=@offset-@mdays
    set @i=@i+1
  end      

  if @offset <= 0
  begin
    set @offset=@offset+@mdays
    set @i=@i-1
  end      

  --确定农历月结束
  set @month=@i    

  --确定农历日结束
  set @day=ltrim(@offset)
  --输出日期
  set @chinesenum= N'〇一二三四五六七八九十'
  while len(@year)>0
  select @outputdate=isnull(@outputdate,'')
         + substring(@chinesenum,left(@year,1)+1,1)
         , @year=stuff(@year,1,1,'')
  set @outputdate=@outputdate+ N'年'
         + case @mleap1 when @month then N'润' else '' end
  if cast(@month as int)<10
    set @outputdate=@outputdate
         + case @month when 1 then N'正'
             else substring(@chinesenum,left(@month,1)+1,1)
           end
  else if cast(@month as int)>=10
    set @outputdate=@outputdate
         + case @month when '10' then N'十' when 11 then N'冬'
           else N'腊' end
  set @outputdate=@outputdate + N'月'
  if cast(@day as int)<10
    set @outputdate=@outputdate + N'初'
         + substring(@chinesenum,left(@day,1)+1,1)
  else if @day between '10' and '19'
    set @outputdate=@outputdate
         + case @day when '10' then N'初十' else N'十'+
           substring(@chinesenum,right(@day,1)+1,1) end
  else if @day between '20' and '29'
    set @outputdate=@outputdate
         + case @day when '20' then N'二十' else N'廿' end
         + case @day when '20' then N'' else
           substring(@chinesenum,right(@day,1)+1,1) end
  else
    set @outputdate=@outputdate+N'三十'
  return @outputdate
END
go


现在我们就可以利用这个函数填充二个农历字段了，分别是chinese_year和lunar_date，因为lunar_year我是准备留用生肖年份（天干地支）用的。
-- 计算农历年份、日期
update date_def
set chinese_year = substring(dbo.fn_cnyear(count_date),1,4),
lunar_date = substring(dbo.fn_cnyear(count_date),6,4)

这一步是最慢的，我的服务器上跑了1分15秒才出来，可能是因为每一个日期都要进行二次函数计算吧，这也是为什么我们需要一张日期定义表的原因了。 最后一步，填充生肖年份，这个代码好像没有现成的，我自己google了一下，写了个小算法，正确是正确了，但可能效率不怎么样，大家就将就点看吧，希望 大家提出更好的方法。
create table #temp1
(flag char(1),
ys smallint,
tgdz varchar(2)
)
insert into #temp1
select 'T',4,'甲'
union all select 'T',5,'乙'
union all select 'T',6,'丙'
union all select 'T',7,'丁'
union all select 'T',8,'戊'
union all select 'T',9,'已'
union all select 'T',0,'庚'
union all select 'T',1,'辛'
union all select 'T',2,'壬'
union all select 'T',3,'癸'
union all select 'D',4,'子'
union all select 'D',5,'丑'
union all select 'D',6,'寅'
union all select 'D',7,'卯'
union all select 'D',8,'辰'
union all select 'D',9,'巳'
union all select 'D',10,'午'
union all select 'D',11,'未'
union all select 'D',0,'申'
union all select 'D',1,'酉'
union all select 'D',2,'戍'
union all select 'D',3,'亥'

declare @year_begin int 
declare @year_end int
declare @lunar_newyear datetime
select @year_begin = min(count_year) from date_def
select @year_end = max(count_year) from date_def

while @year_end >= @year_begin
begin
  select @lunar_newyear = count_date 
   from date_def
   where count_year = @year_begin
    and lunar_date = '正月初一'

  update date_def set lunar_year = b.tgdz+c.tgdz+'年'
  from #temp1 b,#temp1 c
  where count_year%12 = c.ys
  and c.flag = 'D'
  and count_year%10 = b.ys
  and b.flag = 'T'
  and count_year = @year_begin
  and count_date >= @lunar_newyear

  update date_def set lunar_year = b.tgdz+c.tgdz+'年'
  from #temp1 b,#temp1 c  
  where (count_year-1)%12 = c.ys
  and c.flag = 'D'
  and (count_year-1)%10 = b.ys
  and b.flag = 'T'
  and count_year = @year_begin
  and count_date < @lunar_newyear  

  set @year_begin = @year_begin + 1
end
drop table #temp1

注意了，农历的年份和公历可不一定相同的，公历2012年1月9日对应的农历可是二〇一一年辛卯年腊月十六，2012年2月9日对应的农历才是二〇一二年壬辰年正月十八。
好了，这个日期定义表到这里除了需要每年手工修改一次的工作日定义外，就已经全部完工了。我们就可以在以后的日子里，利用这张表去统计一大堆业务部门喜闻乐见，但不知道他们会不会真正用的上的数据吧。