如何生成一个简易年(月)历?

--我也来一个,生成两个日期之间的日期列表declare @begdate datetime    --定义起止日期
declare @enddate datetime
select  @begdate='2000-1-1' --开始日期
select  @enddate='2004-1-1' --结束日期declare @sql varchar(1000)
create table #temp(id int identity(0,1),dt datetime)select @sql='insert into #temp select top '
+cast(datediff(dd,@begdate,@enddate)+1 as varchar)
+' '''+convert(varchar,@begdate,120)+''''
+char(13)+'from(select top 100 id from syscolumns) a'
+char(13)+',(select top 100 id from syscolumns)b'
+char(13)+',(select top 100 id from syscolumns)c'
exec (@sql)
update #temp set dt=dt+id
select * from #tempdrop table #temp

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

--再来一个写成函数的/*--
得到两个日期之间的日期列表

--调用示例
select * from dbo.f_getdate('2003-01-01','2003-01-20')
*/if exists(select 1 from sysobjects where id=object_id('f_getdate') and objectproperty(id,'IsInlineFunction')=0)
drop function f_getdatego
create function f_getdate(@dt1 datetime,@dt2 datetime)
returns @re table(id int identity(0,1),dt datetime)
as
begin
declare @i int
select @i=datediff(day,@dt1,@dt2)+1

while @i>1
begin
insert into @re(dt) select top 100 @dt1 from syscolumns
set @i=@i-100
end
delete from @re where id>datediff(day,@dt1,@dt2)

update @re set dt=dateadd(day,id,dt)
return
end
go
/*--还有: 取得指定年月的日期列表

--调用示例
select * from dbo.fgetdate(2001,11)
*/if exists(select 1 from sysobjects where id=object_id('fgetdate') and objectproperty(id,'IsInlineFunction')=0)
drop function fgetdatego
create function fgetdate(@year int,@month int)
returns @re table(date datetime)
as
begin
declare @dt varchar(10),@i int
select @month=((@month-1) % 12)+1
,@dt=cast(@year as varchar)+'-'
+cast(@month as varchar)+'-01'
,@i=-1
if isdate(@dt)=1
begin
insert into @re
select top 31 @dt from syscolumns
update @re set @i=@i+1,date=date+@i
delete from @re where month(date)<>@month
end
return
end
go
指定年月的日期列表:declare @ datetime
set @ = '2004-02-22'select dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(day,1-day(@),@)))) as [Date]from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
) d
where datediff(month,@,dateadd(day,d.i,dateadd(day,1-day(@),@)))=0
order by [Date]
--增加了日期所在月及年的周次!
--星期日要算在"上一周"!declare @ datetime
set @ = '1995-03-25 11:00:50'
select dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@)))))) as [Date]
,datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) as [WeekDayName]
,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) as [WeekDay]
,datepart(week,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) as WeekOfYear
,datediff
(
week
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))) = '星期日'
           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,1-day(@),dateadd(month,1-month(@),@)))))
      else dateadd(day,0,datediff(day,0,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))
end     ,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) = '星期日'
           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@)))))))
      else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))
end
)
+ 1 as MyWeekOfYear
,datediff(week,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) +1 as WeekOfMonth
,datediff
(week
,case when datename(weekday,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))))= '星期日'
           then dateadd(day,-1,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))))
      else dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@)))))))
end
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) = '星期日'
           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@)))))))
     else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))
end     )
+1 as MyWeekOfMonth,datepart(dayofyear,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) as DayOfYear
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) M
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
) d
where datediff(month,dateadd(day,1-day(@),dateadd(month,1-month(@),@)),dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))) = m.i
order by [Date]
学习，顺便提问题：寻求计算“个人所得税”，“年假”，“员工保险”，“工资计算公式” ，“考勤计算公式”的最佳解决方案。（如何最简单的实现上述功能，但又需要具备很强的通用性和灵活性？）以上工作非常烦琐，花去了我很多时间，搞得我焦头烂额。我知道有很多解决办法，但希望大家能提出一些最优的解决方案。http://expert.csdn.net/Expert/topic/2751/2751870.xml?temp=.7578546
修订版:
http://www.csdn.net/Develop/Read_Article.asp?Id=26083
--2004-04-04 修订
--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)
--注意 datename 的值会因 SQL Server 语言版本或日期格式有所差异!
--本测试环境为: SQL Server 2000 简体中文版 + Windows 简体中文版
declare @ datetime
set @ = '1995-02-25 11:00:50' -- 1995-01-01 正好是个星期日
select @ as 日期
      ,dateadd(year,datediff(year,0,@),0) as 所在年的第一天
      ,dateadd(year,1+datediff(year,0,@),0)-1 as 所在年的最后一天
      ,dateadd(quarter,datediff(quarter,0,@),0) as 所在季的第一天
      ,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as 所在季的最后一天
      ,dateadd(month,datediff(month,0,@),0) as 所在月的第一天
      ,dateadd(month,1+datediff(month,0,@),0)-1 as 所在月的最后一天
      ,dateadd(week,datediff(week,0,@),0) as 所在周的第一天
      ,dateadd(week,1+datediff(week,0,@),0)-1 as 所在周的最后一天
select dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))) as [Date]
,datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDayName]
,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDay]
,datepart(week,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as WeekOfYear
,datediff
(
week
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))) = '星期日'
           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))
      else dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))
end     ,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) = '星期日'
           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
      else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end
)
+ 1 as MyWeekOfYear
,datediff(week,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) +1 as WeekOfMonth
,datediff
(week
,case when datename(weekday,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))))= '星期日'
           then dateadd(day,-1,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))))
      else dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
end
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) = '星期日'
           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
     else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end     )
+1 as MyWeekOfMonth,datepart(dayofyear,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as DayOfYearinto Dfrom
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) M
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
) d
where datediff(month,dateadd(year,datediff(year,0,@),0),dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))) = m.i
order by [Date]
select month(min(date)) as 月份
  ,(select min(date)
      from d
     where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期日' and datediff(month,min(a.date),date) = 0) as 星期日
  , (select min(date)
      from d
     where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期一' and datediff(month,min(a.date),date) = 0) as 星期一
  ,(select min(date)
      from d
     where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期二' and datediff(month,min(a.date),date) = 0) as 星期二
  ,(select min(date)
      from d
     where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期三' and datediff(month,min(a.date),date) = 0) as 星期三
  ,(select min(date)
      from d
     where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期四' and datediff(month,min(a.date),date) = 0) as 星期四
  ,(select min(date)
      from d
     where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期五' and datediff(month,min(a.date),date) = 0) as 星期五
  ,(select min(date)
      from d
     where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期六' and datediff(month,min(a.date),date) = 0) as 星期六from d a
group by datediff(month,0,date),datepart(week,date)