已知表：
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
.........

以下是未经优化的代码,随便看下:

 
-----获取某月的第一天
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]]

三断笛版主很专业.赞一个!