实用SQL语句——用SQL生成日历

pftlwcbf

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 3
select * from dbo.f_calendar(2007,12)

------------------------------
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 @month + '-' + right('00'+cast(t.id as varchar),2) <= dateadd(month , 1 , @month + '-01')
  ) m
) n
group by week
) o


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

DECLARE @d VARCHAR(7),@n INT
SELECT @d='2007-12',@n=DATEDIFF(dd,@d+'-1',DATEADD(mm,1,@d+'-1'))
SET ROWCOUNT @n
SELECT ID=IDENTITY(INT) INTO # FROM sysobjects
SET ROWCOUNT 0
DECLARE @s VARCHAR(8000),@gid INT,@b INT
SELECT @s='',@b=0,@gid=0
SELECT
    @b=DATEPART(wk,@d+'-'+RTRIM(ID)),
    @s=@s + CASE WHEN @b=@gid THEN '    ' ELSE CHAR(10) + REPLICATE('    ',DATEPART(dw,@d+'-'+RTRIM(ID))-1) END + RTRIM(ID) /*RTRIM(DATEPART(dw,@d+'-'+RTRIM(ID)))*/,
    @gid=@b
    FROM #
PRINT '日    一    二    三    四    五    六' + @s
DROP TABLE #

ALTER PROC writeCanlada
(
@d VARCHAR(7)
)
AS
    DECLARE @t TABLE (ID INT IDENTITY(1,1),D VARCHAR(10))
    INSERT @t SELECT TOP 31 'x' FROM sysobjects
    UPDATE @t SET D=@d+'-'+RTRIM(ID)
    DECLARE @s VARCHAR(8000),@gid INT,@b INT
    SELECT @s='',@b=0,@gid=0
    SELECT
        @b=DATEPART(wk,@d+'-'+RTRIM(ID)),
        @s=@s + CASE WHEN @b=@gid THEN '        ' ELSE CHAR(10) + REPLICATE('        ',DATEPART(dw,@d+'-'+RTRIM(ID))-1) END +RTRIM(ID) /*RTRIM(DATEPART(dw,@d+'-'+RTRIM(ID)))*/,
        @gid=@b
        FROM @t WHERE id<=DATEADD(dd,-1,DATEADD(mm,1,@d+'-1')-1)
    PRINT '日          一     二     三     四     五     六' + @s
GO

EXEC writeCanlada '2007-5'
GO
-----------------
declare @days int   --天数
declare @weeks int  --星期
declare @beginDate datetime  --月开始日期
declare @endDate datetime    --月结束日期
declare @Sql varchar(1000)
declare @str varchar(1000)
declare @Month int
set @sql='select ''10'' as [日],''10'' as [一],''10'' as [二],''10'' as [三],''10'' as [四],''10'' as [五],''10'' as [六] where 1=2 '
set @BeginDate='2007-5-1'  --先算5月的 （可以作成函数，存储过程都可以）
set @enddate=dateadd(ms,-3,dateadd(mm,  datediff(m,0,@begindate)+1,  0)) --该月最后一天

set @Month=datepart(mm,@begindate)
set @BeginDate=@BeginDate-datepart(dw,@begindate)+1
while @BeginDate<@EndDate
begin
  set @days=7
  set @weeks=0
  set @Str=''
  while @days<>0
  begin
    if (datepart(dw,@BeginDate)-1)=@weeks and datepart(mm,@begindate)=@Month
    begin
      set @Str=@str+''''+cast(datepart(day,@BeginDate) as char(2))+''','   
    end
    else begin
      set @str=@str+''''''+','
    end
    set @weeks=@weeks+1
    set @days=@days-1
    set @BeginDate=@beginDate+1
  end
  set @str=substring(@str,1,len(@str)-1)
  set @sql=@sql+' union all select '+@Str
end

exec (@sql)
---------------------

alter 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')
-----------------------------------
declare @ym char(6) set @ym = '200712'
declare @mn table (d datetime)
declare @d datetime set @d = convert(datetime,@ym+'01')
while @d<dateadd(month,1,convert(datetime,@ym+'01')) begin
    insert into @mn values (@d)
    set @d=@d+1
end

declare @wds varchar(32)
declare @w int
declare cur_w cursor for select distinct w = datepart(week,d) from @mn

print '日 一 二 三 四 五 六 '
open cur_w
fetch next from cur_w into @w
while @@fetch_status!=-1 begin
    set @wds = ''
    select @wds = @wds + cast(datepart(day,d) as char(3))
    from @mn
    where  datepart(week,d)=@w
    if left(@wds,3)='1  '
        set @wds = right(space(21)+@wds , 21)
    print  @wds
fetch next from cur_w into @w
end
close cur_w
deallocate cur_w

ZNvGQKuO

太历害了，学习了

小小女i

QWEQERQT44444444444444
QWERQERVVVVVVVVVV33444

Qin328

k68882

这个是不是有点太技巧化了，我的意思是，貌似不是很实用哦，数据库的负担太重不好

sky-line

这个是不是有点太技巧化了，我的意思是，貌似不是很实用哦，数据库的负担太重不好

豪仔仔

谢谢楼主提供的好东西。收藏了！

下关砣

牛~~~学习了

伟巴斯特

牛~~~学习了

孤帆

强