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

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'fn_Week)    DROP FUNCTION fn_WeekGOset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO/****************************************************************************** * 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)   asbegin--1.0变量定义和初始化declare @r nvarchar(20)--周次declare @first_day datetime--该月第一天declare @weekday int         --星期几declare @dayof_firstweek int--得到该月第一周有几天declare @dayof_time intdeclare @remainder int--余数set @r = '' set @weekday = 0set @dayof_firstweek = 0set @dayof_time = 0set @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)beginset @first_day=cast((cast(year(@pi_time) as nvarchar(4))+'-'+cast(month(@pi_time) as nvarchar(2))+'-01') as datetime) select @weekday =casewhen datename(weekday,@first_day)='Sunday'then 0--周日when datename(weekday,@first_day)='Monday'then 1--周1when datename(weekday,@first_day)='Tuesday'then 2--周2when datename(weekday,@first_day)='Wednesday'then 3--周3when datename(weekday,@first_day)='Thursday'then 4--周4when datename(weekday,@first_day)='Friday'then 5--周5when datename(weekday,@first_day)='Saturday'then 6--周6  endset @dayof_firstweek = 7 - @weekday--该月第一周有几天--3.1如果传入时间在第一周范围内，周次返回1if(day(@pi_time) <= @dayof_firstweek)begin--3.1.1周次if(@time_type = 'w')beginset @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)endend--3.2如果传入时间不在第一周内if(day(@pi_time) > @dayof_firstweek)beginset @dayof_time=day(@pi_time) + 7 - @dayof_firstweekset @remainder=@dayof_time%7declare @week int--声明临时周次变量set @week = 0--3.2.1余数大于0if(@remainder > 0)begin set @week = 1set @week = @dayof_time/7 + @week --周次end--3.2.2余数等于0if(@remainder = 0)beginset @week = 0set @week = @dayof_time/7 + @week --周次endset @r= cast((@dayof_time/7 + cast(@r as int)) as nvarchar(20))declare @start int--声明该周起始时间天set @start = 0set @start=@dayof_firstweek + 1 + 7*(@week - 2) --开始时间日期（天）declare @end int--声明该周结束时间天set @end = 0set @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')beginset @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')beginset @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)endendendreturn   @rend

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

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