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