sql语句生成日历

[zhang_13245768]

根据年月获取该月的日历

因为我是要在Reporting Service中使用,所以sql语句中不能带有declare和set函数
然后存储过程和函数什么的也不能用,想请问下大家有没有其他的解决办法?

[andysun88]

--创建函数(第一版)(作者：dobear_0922)

 

create function fn_Calendar(@year int, @month int)

 

returns nvarchar(max)

 

as

 

begin

 

    declare @result nvarchar(max), @Enter nvarchar(8)

 

    select @Enter = char(13)+char(10),  @result = ' Sun Mon Tue Wed Thu Fri Sat' + @Enter --表头

 

 

    declare @start datetime, @end datetime

 

    select @start = rtrim(@year)+'-'+rtrim(@month)+'-1', @end = dateadd(mm, 1, @start)    

 

 

 

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

    while datediff(d, @start, @end)>0

 

    begin

 

        if (datepart(dw, @start)+@@datefirst)%7 = 1

 

            select @result = @result+@Enter --是否换行

        select @result = @result+right('   '+rtrim(day(@start)), 4), @start = dateadd(d, 1, @start)

 

    end

 

    return @result

 

end

 

 

 

go

 

--测试示例

set datefirst 3 

 

print dbo.fn_Calendar(2007, 12)

 

select dbo.fn_Calendar(2007, 12)

 

set datefirst 7

 

 

 

--运行结果

/*

 

 Sun Mon Tue Wed Thu Fri Sat

 

                           1

 

   2   3   4   5   6   7   8

 

   9  10  11  12  13  14  15

 

  16  17  18  19  20  21  22

 

  23  24  25  26  27  28  29

 

  30  31

 

*/

 

 

 

go

 

--创建函数(第二版)(作者：libin_ftsafe)

 

create function f_calendar(@year int,@month int)

 

returns @t table(日varchar(4),一varchar(4),二varchar(4),三varchar(4),四varchar(4),五varchar(4),六varchar(4))

 

as

 

begin

 

    declare @a table(id int identity(0,1),date datetime)

 

    insert into @a(date) 

 

    select top 31 rtrim(@year)+'-'+rtrim(@month)+'-1' from sysobjects

 

    update @a set date=dateadd(dd,id,date)    

 

    insert into @t

 

    select

 

        max(case datepart(dw,date) when 7 then rtrim(day(date)) else '' end),

 

        max(case datepart(dw,date) when 1 then rtrim(day(date)) else '' end),

 

        max(case datepart(dw,date) when 2 then rtrim(day(date)) else '' end),

 

        max(case datepart(dw,date) when 3 then rtrim(day(date)) else '' end),

 

        max(case datepart(dw,date) when 4 then rtrim(day(date)) else '' end),

 

        max(case datepart(dw,date) when 5 then rtrim(day(date)) else '' end),

 

        max(case datepart(dw,date) when 6 then rtrim(day(date)) else '' end)

 

    from

 

        @a

 

    where

 

        month(date)=@month

 

    group by

 

        (case datepart(dw,date) when 7 then datepart(week,date)+1 else datepart(week,date) end)

 

 

 

    return

 

end

 

 

 

go

 

--测试示例

set datefirst 1

 

select * from dbo.f_calendar(2007,12)

 

 

 

--运行结果

/*

 

日  一  二  三  四  五  六

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

 

                              1

 

2    3    4    5    6    7    8

 

9    10   11   12   13   14   15

 

16   17   18   19   20   21   22

 

23   24   25   26   27   28   29

 

30   31                  

 

*/

 

 

[dawugui]

declare @month as varchar(7)
set @month = '2007-12'

select 日,一,二,三,四,五,六 from
(
select week , 
  max(case weekday when 1 then datename(day,dt) else '' end ) '日',
  max(case weekday when 2 then datename(day,dt) else '' end ) '一',
  max(case weekday when 3 then datename(day,dt) else '' end ) '二',
  max(case weekday when 4 then datename(day,dt) else '' end ) '三',
  max(case weekday when 5 then datename(day,dt) else '' end ) '四',
  max(case weekday when 6 then datename(day,dt) else '' end ) '五',
  max(case weekday when 7 then datename(day,dt) else '' end ) '六'
from
(
  select week = datepart(week , m.dt) , weekday = datepart(weekday , m.dt) , dt from
  (
    select dt = @month + '-' + right('00'+cast(t.id as varchar),2) from
    (
      select 1 as id union select 2 union select 3 union select 4 union select 5
      union select 6 union select 7 union select 8 union select 9 union select 10
      union select 11 union select 12 union select 13 union select 14 union select 15
      union select 16 union select 17 union select 18 union select 19 union select 20
      union select 21 union select 22 union select 23 union select 24 union select 25
      union select 26 union select 27 union select 28 union select 29 union select 30
      union select 31
    ) t
    where isdate(@month + '-' + right('00'+cast(t.id as varchar),2)) = 1 and @month + '-' + right('00'+cast(t.id as varchar),2) <= dateadd(month , 1 , @month + '-01')
  ) m
) n
group by week
) o
/*
日  一  二  三  四  五  六                              
--  --  --  --  --  --  --
                        1
2   3   4   5   6   7   8
9   10  11  12  13  14  15
16  17  18  19  20  21  22
23  24  25  26  27  28  29
30  31                                                                                                                                                         

（所影响的行数为 6 行）
*/

[--小F--]

/*
功能：2000当中绘画日历 
*/
DECLARE @Year nvarchar(4)
DECLARE @YearMonth nvarchar(7)    --月份
DECLARE @strTop nvarchar(200)
DECLARE @ForI INT,@ForYear INT ,@MaxDay INT
DECLARE @RowX INT --行位置 
DECLARE @strWeekDayList nvarchar(20)
DECLARE @strPrint nvarchar(300)

-- ======================================
SET @Year='2008'    --请在这里输入年份
-- ======================================
SET @strTop= '日'+char(9)+'一'+char(9)+'二' +char(9)+'三'++char(9)+'四'++char(9)+'五'++char(9)+'六' +char(13)+
         '───────────────────────────'
SET @strWeekDayList='日一二三四五六'
SET @ForYear=1
WHILE @ForYear<=12  --1月份至12月份
 BEGIN
    SET @YearMonth=@Year + '-' +CAST( @ForYear AS nvarchar(2))
    SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+'-01')))
    SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+'-01'),1),@strWeekDayList)-1
    SET @strPrint=''
    SET @ForI=1
    WHILE @ForI<=@RowX    --构造1号的位置
         BEGIN
        SET @strPrint=@strPrint+CHAR(9)
        SET @ForI=@ForI+1
         END
        SET @ForI=1
    WHILE @ForI<=@MaxDay    --构造2号到月底的位置
         BEGIN
        SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9)    
        SET @RowX=@RowX+1
        SET @ForI=@ForI+1
      IF (@RowX%7=0)
           BEGIN
            SET @RowX=0 
            SET @strPrint=@strPrint+CHAR(13)
             END
       END
    SET @ForYear=@ForYear+1
    -- 打印输出一个月的结果
    PRINT '━━━━━━━━━━━━━━━━━━━━━━━━━━━'
    PRINT +Char(9)++Char(9)+'    '+@YearMonth+CHAR(10)
    PRINT @strTop
    PRINT @strPrint+CHAR(10)
  END

[王向飞]

找找有没有这方面的插件

[快溜]

做个日历玩玩

[zhang_13245768]

不知道1楼 2楼 3楼有没有看清我的需求 说了在reporting services中不能使用函数和存储过程
不能用declare和set 就是想用普通的sql语句写出来

[王向飞]

这有难度 用CTE写

coleling应该会帮你写

[zhang_13245768]

[Quote=引用 7 楼 wxf163 的回复:]
这有难度 用CTE写

coleling应该会帮你写
[/Quote]

没见过 啥东西

[zhang_13245768]

哪位用过reporting services吗 怎么在里面生成日历呢