整理了SQL Server中是實現日曆的幾個方法

taogchan發表於2014-01-09
SQLServer

  1. create function fn_Calendar(@year int, @month int)
  2. returns nvarchar(max)
  3. as
  4. begin
  5.     declare @result nvarchar(max), @Enter nvarchar(8)
  6.     select @Enter = char(13)+char(10), @result = \' Sun Mon The Wed Thu Fri Sta\' + @Enter --表頭


  9.     declare @start datetime, @end datetime
  10.     select @start = rtrim(@year)+\'-\'+rtrim(@month)+\'-1\', @end = dateadd(mm, 1, @start)

  12.     set @result = @result+replicate(\' \', (datepart(dw, @start)+@@datefirst+6)%7) --第一行前面的空格

  14.     while datediff(d, @start, @end)>0
  15.     begin
  16.         if (datepart(dw, @start)+@@datefirst)%7 = 1
  17.             select @result = @result+@Enter --是否換行

  19.         select @result = @result+right(\' \'+rtrim(day(@start)), 4), @start = dateadd(d, 1, @start)
  20.     end
  21.     return @result
  22. end
  23. go

  25. set datefirst 3
  26. print dbo.fn_Calendar(2007, 12)
  27. select dbo.fn_Calendar(2007, 12)
  28. set datefirst 7

  30. drop function dbo.fn_Calendar

  32. /*
  33.  Sun Mon The Wed Thu Fri Sta
  34.                            1
  35.    2 3 4 5 6 7 8
  36.    9 10 11 12 13 14 15
  37.   16 17 18 19 20 21 22
  38.   23 24 25 26 27 28 29
  39.   30 31

  41. ------------------------------------------
  42.  Sun Mon The Wed Thu Fri Sta
  43.                            1
  44.    2 3 4 5 6 7 8
  45.    9 10 11 12 13 14 15
  46.   16 17 18 19 20 21 22
  47.   23 24 25 26 27 28 29
  48.   30 31

  50. (1 row(s) affected)
  51. */
 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  • create function F_month(@YMonth nvarchar(6))
  • returns @T table(日 varchar(4),一 varchar(4),二 varchar(4),三 varchar(4),四 varchar(4),五 varchar(4),六 varchar(4))
  • as
  • begin
  •     declare @Tmp table([weekday] int,[day] nvarchar(2),[group] int)---增加一列作為分組顯示

  •     declare @i int,@j int,@date datetime,@group int
  •     select @date=@YMonth+\'01\',@i=datediff(dd,@date,dateadd(month,1,@date)),@j=0,@group=0
  •     while @i>@j
  •     begin
  •         insert @Tmp select (datepart(dw,@date)+@@datefirst-1)%7,datepart(d,@date),case when (datepart(dw,@date)+@@datefirst-1)%7=0 then @group+1 else @group end
  •         select @j=@j+1,@group=case when (datepart(dw,@date)+@@datefirst-1)%7=0 then @group+1 else @group end,@date=dateadd(dd,1,@date)
  •     end
  •     insert @T
  •     select
  •         max(case when [weekday]=0 then [day] else \'\' end),
  •         max(case when [weekday]=1 then [day] else \'\' end),
  •         max(case when [weekday]=2 then [day] else \'\' end),
  •         max(case when [weekday]=3 then [day] else \'\' end),
  •         max(case when [weekday]=4 then [day] else \'\' end),
  •         max(case when [weekday]=5 then [day] else \'\' end),
  •         max(case when [weekday]=6 then [day] else \'\' end)
  •     from
  •         @Tmp
  •     group by [group]
  •     return
  • end


  • go
  • select * from F_month(\'0712\')
  • 或:
  • select * from F_month(\'200712\')

    • ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


    資料庫應該建立一個日曆表

     
    CREATE TABLE Calendar(
     date datetime NOT NULL PRIMARY KEY CLUSTERED,
     weeknum int NOT NULL,
     weekday int NOT NULL,
     weekday_desc nchar(3) NOT NULL,
     is_workday bit NOT NULL,
     is_weekend bit NOT NULL
    )
    GO
    WITH CTE1 AS(
     SELECT
      date = DATEADD(day,n,'19991231')
     FROM Nums
     WHERE n <= DATEDIFF(day,'19991231','20201231')),
    CTE2 AS(
     SELECT
      date,
      weeknum = DATEPART(week,date),
      weekday = (DATEPART(weekday,date) + @@DATEFIRST - 1) % 7,
      weekday_desc = DATENAME(weekday,date)
     FROM CTE1)
    --INSERT INTO Calendar
    SELECT
     date,
     weeknum,
     weekday,
     weekday_desc,
     is_workday = CASE WHEN weekday IN (0,6) THEN 0 ELSE 1 END,
     is_weekend = CASE WHEN weekday IN (0,6) THEN 1 ELSE 0 END
    FROM CTE2

    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22392018/viewspace-1068772/，如需轉載，請註明出處，否則將追究法律責任。

    相關文章