|
#2
三断笛2008-10-25 03:19
以下是未经优化的代码,随便看下:
 程序代码：-----获取某月的第一天 create function get_Firstday(@days datetime) returns datetime begin return cast(cast(year(@days) as char(4))+'-'+ cast(month(@days) as nvarchar(2))+'-01' as datetime) end 程序代码：-----获取某月的最后一天 create function get_Lastday(@days datetime) returns datetime begin return dateadd(day,-1,dbo.get_firstday(dateadd(month,1,@days))) end 程序代码：----获取某月的长度 create function get_days(@days datetime) returns int begin return datediff(day,dbo.get_firstday(@days),dbo.get_lastday(@days))+1 end 程序代码：---获取某月的周末数 create function get_weekdays(@days datetime) returns int begin declare @ret int; declare @weekday int; declare @leftdays int; set @weekday=datepart(weekday,dbo.get_firstday(@days)); set @leftdays=dbo.get_days(@days) % 7 if @leftdays=0 return 8; -----28天的周末只有8天 if @leftdays=1 begin if @weekday=7 or @weekday=1 return 9 else return 8 end if @leftdays=2 begin if @weekday=7 return 10 else if @weekday=1 or @weekday=6 return 9 else return 8 end if @leftdays=3 begin if @weekday=6 or @weekday=7 return 10 else if @weekday=1 or @weekday=5 return 9 else return 8 end return 0 end 程序代码：---使用方法如: select id,time,dbo.get_weekdays(time) from 表名 经测试有效. [[it] 本帖最后由 三断笛 于 2008-10-25 03:21 编辑 [/it]] |
已知表：
id time
1 2007-10-20 9:30:02
2 2008-11-20 9:30:02
3 2008-10-20 9:30:02
4 2009-2-20 9:30:02
.........
想得出下面的表,weekdays是time里的月份里所包含周末的天数，即当月周六和周日一共的天数
id time weekdays
1 2007-10-20 9:30:02 8
2 2008-11-20 9:30:02 10
3 2008-10-20 9:30:02 8
4 2009-10-20 9:30:02 9
.........
