declare @month as varchar(7) set @month = '2007-12'select week , max(case weekday when 1 then dt else '' end ) '日', max(case weekday when 2 then dt else '' end ) '一', max(case weekday when 3 then dt else '' end ) '二', max(case weekday when 4 then dt else '' end ) '三', max(case weekday when 5 then dt else '' end ) '四', max(case weekday when 6 then dt else '' end ) '五', max(case weekday when 7 then 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/* week 日 一 二 三 四 五 六 ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ 48 2007-12-01 49 2007-12-02 2007-12-03 2007-12-04 2007-12-05 2007-12-06 2007-12-07 2007-12-08 50 2007-12-09 2007-12-10 2007-12-11 2007-12-12 2007-12-13 2007-12-14 2007-12-15 51 2007-12-16 2007-12-17 2007-12-18 2007-12-19 2007-12-20 2007-12-21 2007-12-22 52 2007-12-23 2007-12-24 2007-12-25 2007-12-26 2007-12-27 2007-12-28 2007-12-29 53 2007-12-30 2007-12-31 (所影响的行数为 6 行)*/
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/* 日 一 二 三 四 五 六 ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 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 行) */
CREATE TABLE [dbo].[cfg_xx_datename] ( [date] [datetime] NOT NULL PRIMARY KEY, [mName] as convert(char(6),[date],112) , -- Example: '200710' [weekName] char(3) -- Example: 'W05' ) ON [PRIMARY] GO-- 为快速计算登记数据 declare @dt datetime set @dt = '2007-3-1' while @dt<'2009-3-1' begin insert [dbo].[cfg_xx_datename] ([date]) values (@dt) set @dt = @dt+1 end go update cfg_xx_datename set [weekName] = 'W'+right(cast(datediff(week,( select min([date]) from cfg_xx_datename t where t.mName=a.mName),[date])+101 as varchar),2) from cfg_xx_datename a go -- 指定月份 declare @mn char(6) set @mn='200711' -- 输出日历 select [SUN]=w0.[day] ,[MON]=w1.[day] ,[TUE]=w2.[day] ,[WED]=w3.[day] ,[THU]=w4.[day] ,[FRI]=w5.[day] ,[SAT]=w6.[day] from (select distinct weekName from cfg_xx_datename where mName = @mn) as a left join -- Sun: (select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=1) as w0 on w0.weekName=a.weekName left join -- Mon: (select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=2) as w1 on w1.weekName=a.weekName left join -- Tue: (select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=3) as w2 on w2.weekName=a.weekName left join -- Wed: (select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=4) as w3 on w3.weekName=a.weekName left join -- Thu: (select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=5) as w4 on w4.weekName=a.weekName left join -- Fri: (select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=6) as w5 on w5.weekName=a.weekName left join -- Sat: (select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=7) as w6 on w6.weekName=a.weekName go -- Clear drop table [cfg_xx_datename] go
11月份的. declare @month as varchar(7) set @month = '2007-11'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 行) */
--上面11月份结果没替换. declare @month as varchar(7) set @month = '2007-11'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 (所影响的行数为 5 行)*/
create function fn_Calendar(@year int, @month int, @datefirst tinyint) returns nvarchar(max) as begin declare @result nvarchar(max), @Enter nvarchar(8) select @Enter = char(13)+char(10), @result = ' Sun Mon The Wed Thu Fri Sta' + @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 goset datefirst 3 -- set datefirst 7 print dbo.fn_Calendar(2007, 12, @@datefirst) select dbo.fn_Calendar(2007, 12, @@datefirst)drop function dbo.fn_Calendar/* Sun Mon The Wed Thu Fri Sta 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------------------------------------------ Sun Mon The Wed Thu Fri Sta 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(1 row(s) affected) */
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 The Wed Thu Fri Sta' + @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 goset datefirst 3 print dbo.fn_Calendar(2007, 12) select dbo.fn_Calendar(2007, 12) set datefirst 7drop function dbo.fn_Calendar/* Sun Mon The Wed Thu Fri Sta 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------------------------------------------ Sun Mon The Wed Thu Fri Sta 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(1 row(s) affected) */
用我的方法,不管datefirst设置为几都不会出错,呵呵
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 # /* 日 一 二 三 四 五 六 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 */
存储过程 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<=DATEPART(dd,DATEADD(dd,-1,DATEADD(mm,1,@d+'-1')-1)) PRINT '日 一 二 三 四 五 六' + @s EXEC writeCanlada '2007-11'
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 endexec (@sql) --结果 日 一 二 三 四 五 六 ---- ---- ---- ---- ---- ---- ---- 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
报歉.18楼语句多写了一个DATEADD 另外,输出的东西是tab符,不是空格.空格格式会乱,csdn给我改的.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 GOEXEC writeCanlada '2007-12' GO
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),[week] int) declare @i int,@j int,@date datetime select @date=@YMonth+'01',@i=datediff(dd,@date,dateadd(month,1,@date)),@j=0 while @i>@j begin insert @Tmp select (datepart(dw,@date)+@@datefirst-1)%7,datepart(d,@date),datepart(wk,@date) select @j=@j+1,@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 [week] return end go select * from F_month('0712') 或: select * from F_month('200712')日 一 二 三 四 五 六 ---- ---- ---- ---- ---- ---- ---- 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 行)
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')--drop function F_month
再来一个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 enddeclare @wds varchar(32) declare @w int declare cur_w cursor for select distinct w = datepart(week,d) from @mnprint '日 一 二 三 四 五 六 ' 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
set @month = '2007-12'select week ,
max(case weekday when 1 then dt else '' end ) '日',
max(case weekday when 2 then dt else '' end ) '一',
max(case weekday when 3 then dt else '' end ) '二',
max(case weekday when 4 then dt else '' end ) '三',
max(case weekday when 5 then dt else '' end ) '四',
max(case weekday when 6 then dt else '' end ) '五',
max(case weekday when 7 then 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/*
week 日 一 二 三 四 五 六
----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------
48 2007-12-01
49 2007-12-02 2007-12-03 2007-12-04 2007-12-05 2007-12-06 2007-12-07 2007-12-08
50 2007-12-09 2007-12-10 2007-12-11 2007-12-12 2007-12-13 2007-12-14 2007-12-15
51 2007-12-16 2007-12-17 2007-12-18 2007-12-19 2007-12-20 2007-12-21 2007-12-22
52 2007-12-23 2007-12-24 2007-12-25 2007-12-26 2007-12-27 2007-12-28 2007-12-29
53 2007-12-30 2007-12-31 (所影响的行数为 6 行)*/
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
48 1
49 2 3 4 5 6 7 8
50 9 10 11 12 13 14 15
51 16 17 18 19 20 21 22
52 23 24 25 26 27 28 29
53 30 31 (所影响的行数为 6 行)
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/*
日 一 二 三 四 五 六
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
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 行)
*/
CREATE TABLE [dbo].[cfg_xx_datename] (
[date] [datetime] NOT NULL PRIMARY KEY,
[mName] as convert(char(6),[date],112) , -- Example: '200710'
[weekName] char(3) -- Example: 'W05'
) ON [PRIMARY]
GO-- 为快速计算登记数据
declare @dt datetime set @dt = '2007-3-1'
while @dt<'2009-3-1' begin
insert [dbo].[cfg_xx_datename] ([date]) values (@dt)
set @dt = @dt+1
end
go
update cfg_xx_datename set
[weekName] = 'W'+right(cast(datediff(week,(
select min([date]) from cfg_xx_datename t
where t.mName=a.mName),[date])+101 as varchar),2)
from cfg_xx_datename a
go
-- 指定月份
declare @mn char(6) set @mn='200711'
-- 输出日历
select
[SUN]=w0.[day]
,[MON]=w1.[day]
,[TUE]=w2.[day]
,[WED]=w3.[day]
,[THU]=w4.[day]
,[FRI]=w5.[day]
,[SAT]=w6.[day]
from
(select distinct weekName from cfg_xx_datename where mName = @mn) as a
left join
-- Sun:
(select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=1) as w0 on w0.weekName=a.weekName
left join
-- Mon:
(select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=2) as w1 on w1.weekName=a.weekName
left join
-- Tue:
(select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=3) as w2 on w2.weekName=a.weekName
left join
-- Wed:
(select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=4) as w3 on w3.weekName=a.weekName
left join
-- Thu:
(select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=5) as w4 on w4.weekName=a.weekName
left join
-- Fri:
(select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=6) as w5 on w5.weekName=a.weekName
left join
-- Sat:
(select [day]=day([date]), weekName from cfg_xx_datename where mName = @mn and datepart(weekday,[date])=7) as w6 on w6.weekName=a.weekName
go
-- Clear
drop table [cfg_xx_datename]
go
declare @month as varchar(7)
set @month = '2007-11'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 行)
*/
declare @month as varchar(7)
set @month = '2007-11'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 (所影响的行数为 5 行)*/
returns nvarchar(max)
as
begin
declare @result nvarchar(max), @Enter nvarchar(8)
select @Enter = char(13)+char(10), @result = ' Sun Mon The Wed Thu Fri Sta' + @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
goset datefirst 3 -- set datefirst 7
print dbo.fn_Calendar(2007, 12, @@datefirst)
select dbo.fn_Calendar(2007, 12, @@datefirst)drop function dbo.fn_Calendar/*
Sun Mon The Wed Thu Fri Sta
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------------------------------------------
Sun Mon The Wed Thu Fri Sta
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(1 row(s) affected)
*/
returns nvarchar(max)
as
begin
declare @result nvarchar(max), @Enter nvarchar(8)
select @Enter = char(13)+char(10), @result = ' Sun Mon The Wed Thu Fri Sta' + @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
goset datefirst 3
print dbo.fn_Calendar(2007, 12)
select dbo.fn_Calendar(2007, 12)
set datefirst 7drop function dbo.fn_Calendar/*
Sun Mon The Wed Thu Fri Sta
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------------------------------------------
Sun Mon The Wed Thu Fri Sta
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(1 row(s) affected)
*/
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 #
/*
日 一 二 三 四 五 六
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
*/
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<=DATEPART(dd,DATEADD(dd,-1,DATEADD(mm,1,@d+'-1')-1))
PRINT '日 一 二 三 四 五 六' + @s
EXEC writeCanlada '2007-11'
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
endexec (@sql)
--结果
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
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
另外,输出的东西是tab符,不是空格.空格格式会乱,csdn给我改的.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
GOEXEC writeCanlada '2007-12'
GO
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),[week] int)
declare @i int,@j int,@date datetime
select @date=@YMonth+'01',@i=datediff(dd,@date,dateadd(month,1,@date)),@j=0
while @i>@j
begin
insert @Tmp select (datepart(dw,@date)+@@datefirst-1)%7,datepart(d,@date),datepart(wk,@date)
select @j=@j+1,@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 [week]
return
end
go
select * from F_month('0712')
或:
select * from F_month('200712')日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
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 行)
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')--drop function F_month
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
enddeclare @wds varchar(32)
declare @w int
declare cur_w cursor for select distinct w = datepart(week,d) from @mnprint '日 一 二 三 四 五 六 '
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