sqlserver日期数组

with num as
(
    select number i
      from master.dbo.spt_values
     where type='p' and number <=9
),
c_num as
(
    SELECT n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 AS number
    FROM
      num n1
      CROSS JOIN num AS n10
      CROSS JOIN num AS n100
      CROSS JOIN num AS n1000
    --order by 1
),
  c_date as
  (
      select convert(varchar(10), DATEADD(DD, number, '2019-03-01'), 120) AS tma
      from c_num
      where number <= DATEDIFF(DD, '2019-03-01', '2019-12-31')
      --order by 1
  ),
n1 as (
select
    CONVERT(datetime,tma,101) as the_date,
    tma as dt_10,
    replace(tma,'-','') as dt_8,
    DATENAME (dw, tma) as the_day,
    datepart(weekday, tma) as the_dayy,
    DATENAME (mm,tma) the_month,
    DATENAME (yy, tma) the_year,substring(tma,9,2) as day_of_month,
    case when DATENAME (dw, tma)='星期日' then datepart(week,tma)-1 else datepart(week,tma) end week_of_year,
    --case when len(DATENAME (week, tma))=1 then '0'+ DATENAME (week, tma)  else DATENAME (week, tma)end week_of_year,
    'Q' +  CAST(DATENAME (quarter, tma)as varchar(1)) as 'quarter'
   ,substring(CONVERT(varchar(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,CONVERT(datetime,tma,101))+1,0)),120),9,2) as diff_m,--本月多少天
          datediff(day,dateadd(qq,datediff(qq,0,getdate()),0),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))+1 as diff_q--本季度多少天
    ,case when DATENAME (dw, tma)='星期日'then convert(varchar(10),dateadd(day,-6,tma),120)
         else convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,tma),0),120) end as start_w  --周第一天
    ,convert(varchar(10),dateadd(d,-day(CONVERT(datetime,tma,101))+1,CONVERT(datetime,tma,101)),120) as start_m--月第一天
    ,convert(varchar(10),dateadd(qq,datediff(qq,0,CONVERT(datetime,tma,101)),0),120) as start_q--季度第一天
    ,CONVERT(varchar(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,CONVERT(datetime,tma,101))+1,0)),120) as end_m-- 每月的最后一天

from c_date),
n2 as (
select
       dt_8  ,---日主键PK_DATE,
      dt_10  ,---日主键PK_DATE,
       the_date,
       the_day,
       the_dayy,
       the_month,
       the_year as the_year,day_of_month,
       case when len(DATENAME (week, week_of_year))=1 then '0'+ convert(varchar(2),week_of_year)  else convert(varchar(2),week_of_year) end week_of_year,
       quarter,
       diff_m,
       diff_q,
       start_w,
       start_m,
       start_q,
       end_m
from n1  )

select       dt_8   AS WD08_06,---日主键PK_DATE,
     dt_10 AS WD08_07,---日主键PK_DATE,
      dt_10  AS WD08_08,---日主键PK_DATE,
  ----通过【日】计算【年】----------------------------------------------------------------------
     substring(dt_8,1,4)  AS  WD08_09,--PK_YEAR,年
         substring(dt_8,1,4)+'年' AS  WD08_10,--YEAR_NAME,年名称
  ----通过【日】计算【季】----------------------------------------------------------------------
     substring(quarter,2,1)   AS  WD08_11,--PK_QUARTER,季度,4
       quarter AS  WD08_12,--QUARTER_ABB,季度简写,Q4
        substring(quarter,2,1)+'季度' AS  WD08_13,--QUARTER_NAME,季度名称,4季度
      substring(dt_8,1,4)+'年'+ substring(quarter,2,1)+'季度' AS  WD08_14,-- YEARQUARTER_ABB,年月季度简写,2018-Q4

  ----通过【日】计算【月】----------------------------------------------------------------------
      the_month  AS  WD08_15,--PK_MONTH,月
      the_month+'月'  AS  WD08_16,--MONTH_NAME,月名称
     case when the_month like  '0%' then substring(the_month,2,1) else the_month end  AS  WD08_17,-- PK_MONTH_FMMM,月(去掉0和空格)
     case when the_month like  '0%' then substring(the_month,2,1) else the_month end +'月' AS  WD08_18,--MONTH_NAME_FMMM,月名称(去掉0和空格)
    substring(dt_8,1,6)   AS WD08_19,--PK_YEARMONTH,年月,YYYYMM
     substring(dt_10,1,7)  AS WD08_20,--PK_YEARMONTH,年月,YYYY-MM
  substring(dt_8,1,4)+'年'+the_month+'月' AS WD08_21, --YEARMONTH_NAME,年月名称,2018年10月

   -----旬
  case when right(dt_8,2)<=10 then '上旬' when  right(dt_8,2)<=20 then '中旬' else '下旬' end AS WD08_22,  --计算旬（中文）
  case when right(dt_8,2)<=10 then '1' when  right(dt_8,2)<=20 then '2' else '3' end  AS WD08_23, -- 旬（英文） '1' - 上旬  '2'-中旬 '3'-下旬

  ----通过【日】计算【周】----------------------------------------------------------------------
right(week_of_year,2)  AS  WD24,-- YEAR_WEEK_NUM,周数
 substring(dt_8,1,4)+'年—W'+ right(week_of_year,2)  AS  WD25,-- PK_YEARWEEK, 年周
  '第'+right(week_of_year,2)+'周'  AS WD26,--YEAR_WEEK_NAME,第X周,ISO标准周

  ----通过【日】计算【日】----------------------------------------------------------------------
dt_8   AS WD27,   --年日(YYYYMMDD)
CONVERT(varchar,the_date,111)  AS WD28,    --年日(YYYY/MM/DD)
  substring(dt_8,1,4)+'年'+the_month+'月'+ right(dt_8,2)+'日'  AS WD29,   --年日(中文)

  ----其他----------------------------------------------------------------------
convert(varchar(10),dateadd(yy,datediff(yy,0,the_date),0),120)  AS WD30,--YEAR_BEGIN_DATE,年_开始日期
convert(varchar(10),dateadd(year, datediff(year, 0, dateadd(year, 1, the_date)), -1),120) AS  WD31,--YEAR_END_DATE,年_截至日期
  DATEDIFF ( dd , dateadd(year, datediff(year, 0, the_date), 0) , dateadd(year, datediff(year, 0, dateadd(year, 1, the_date)), -1) )+1 AS  WD32,--YEAR_DAYS,年天数
  case when dt_10 = convert(varchar(10),dateadd(year, datediff(year, 0, dateadd(year, 1, the_date)), -1),120) then 1 else 0 end  AS WD33, ---IS_LASTDAY_YEAR,判断是否为年末最后一天
datediff(dd,convert(varchar(10),dateadd(yy,datediff(yy,0,the_date),0),120),the_date)+1 AS WD34,--YEAR_DAY,日在年中第几天
  datediff(dd,start_q,the_date)+1 AS WD35,--QUARTER_DAY,日在季中第几天
start_q   AS  WD36,--QUARTER_BEGIN_DATE,季度_开始日期,2018-10-01
convert(varchar(10),DATEADD(DAY,diff_q-1,the_date),120)  AS  WD37,--QUARTER_END_DATE,季度_截至日期,2018-12-31
diff_q AS  WD38,--QUARTER_DAYS,季度天数
case when dt_10 =convert(varchar(10),DATEADD(DAY,diff_q-1,the_date),120) then 1 else 0 end AS WD39,--IS_LASTDAY_QUARTER,判断是否为季度末最后一天
right(dt_8,2) AS WD34,--YEAR_DAY,日在年中第几天
  start_m AS  WD41,--MONTH_BEGIN_DATE,月_开始日期
 convert(varchar(10),dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,the_date)+1, 0)),120)  AS  WD42,--MONTH_END_DATE,月_截至日期
  diff_m AS WD43,--MONTH_DAYS,月天数
  case when dt_10=convert(varchar(10),dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,the_date)+1, 0)),120)  then 1 else 0 end  AS WD44,-- IS_LASTDAY_MONTH,判断是否为月末最后一天
  datediff(dd,start_w,the_date)+1 AS WD45,--WEEK_NUM, 周内第几天
  the_day AS WD46,--WEEK_NAME,  星期几
  start_w AS WD47,--WEEK_BEGIN_DATE,本周第一天
  convert(varchar(10),dateadd(dd,6,start_w),120)  AS WD48,--WEEK_END_DATE,本周最后一天
  case when dt_10=convert(varchar(10),dateadd(dd,6,start_w),120)  then 1 else 0 end  AS WD49, ---IS_LASTDAY_WEEK,判断是否为周末最后

  the_date
from n2 order by 1