【原创】SQL 返回时间所在月份周次，该周起始结束日期

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'fn_Week)
    DROP FUNCTION fn_Week
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/******************************************************************************
 * System Name : 
 * fn Name     : fn_Week
 * Summary     : 获取当前时间所在月份的周次 
 * Parameter   :
 *              [Input]
 *               @pi_time datetime
 *
 * Remark      : Nothing
 *
 * History     : 2013/12/09 
 *
 *****************************************************************************/
CREATE function [dbo].[fn_Week](
@pi_time datetime, --传入时间
@time_type nvarchar(3) --时间类型，w-返回周数，s-返回该周开始时间，e-返回该周结束时间
)
returns nvarchar(4000)   
as
begin
--1.0变量定义和初始化
declare @r nvarchar(20)--周次
declare @first_day datetime--该月第一天
declare @weekday int         --星期几
declare @dayof_firstweek int--得到该月第一周有几天
declare @dayof_time int
declare @remainder int--余数


set @r = '' 
set @weekday = 0
set @dayof_firstweek = 0
set @dayof_time = 0
set @remainder  = 0


--2.0如果传入时间的日期是所在月第一天，则肯定是第一周
if (day(@pi_time) = 1)
begin
--2.1周次
if(@time_type = 'w')
set @r='1'

--2.2开始时间
if(@time_type = 's')
set @r=cast((cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-01') as datetime) 


--2.3结束时间
if(@time_type = 'e')
set @r=cast((cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-07') as datetime) 
end


--3.0如果传入时间的日期不是所在月第一天  
if (day(@pi_time) != 1)
begin
set @first_day=cast((cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-01') as datetime) 
select @weekday =case
when datename(weekday,@first_day)='Sunday'then 0--周日
when datename(weekday,@first_day)='Monday'then 1--周1
when datename(weekday,@first_day)='Tuesday'then 2--周2
when datename(weekday,@first_day)='Wednesday'then 3--周3
when datename(weekday,@first_day)='Thursday'then 4--周4
when datename(weekday,@first_day)='Friday'then 5--周5
when datename(weekday,@first_day)='Saturday'then 6--周6
  end
set @dayof_firstweek = 7 - @weekday--该月第一周有几天

--3.1如果传入时间在第一周范围内，周次返回1
if(day(@pi_time) <= @dayof_firstweek)
begin
--3.1.1周次
if(@time_type = 'w')
begin
set @r='1'
end
--3.1.2开始时间
if(@time_type = 's')
begin 
set @r=cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-01' 
--set @r=convert(varchar(100),@r,23)
end


--3.1.3结束时间
if(@time_type = 'e')
begin 
set @r=cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-'+cast(@dayof_firstweek as nvarchar(2)) 
--set @r=convert(nvarchar(100),@r,23)
end
end

--3.2如果传入时间不在第一周内
if(day(@pi_time) > @dayof_firstweek)
begin
set @dayof_time=day(@pi_time) + 7 - @dayof_firstweek
set @remainder=@dayof_time%7


declare @week int--声明临时周次变量
set @week = 0


--3.2.1余数大于0
if(@remainder > 0)
begin 
set @week = 1
set @week = @dayof_time/7 + @week --周次
end


--3.2.2余数等于0
if(@remainder = 0)
begin
set @week = 0
set @week = @dayof_time/7 + @week --周次
end

set @r= cast((@dayof_time/7 + cast(@r as int)) as nvarchar(20))


declare @start int--声明该周起始时间天
set @start = 0
set @start=@dayof_firstweek + 1 + 7*(@week - 2) --开始时间日期（天）


declare @end int--声明该周结束时间天
set @end = 0
set @end=@dayof_firstweek  + 7*(@week - 1) --结束时间日期（天）

--3.2.3周次
if(@time_type = 'w')
set @r=cast(@week as nvarchar(20))

--3.2.4开始时间
if(@time_type = 's')
begin
set @r=cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-'+cast(@start as nvarchar(2))
--set @r=convert(nvarchar(100),@r,23)
end

--3.2.5结束时间
if(@time_type = 'e')
begin
set @r=cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-'+cast(@end as nvarchar(2))
--set @r=convert(nvarchar(100),@r,23)
end
end
end
return   @r
end

----调用（2013-12-09）

select dbo.fn_Week(getdate(),'w')
--2
select dbo.fn_Week(getdate(),'s')
--2013-12-8
select dbo.fn_Week(getdate(),'e')
--2013-12-14