工作中需要根据日期返回上一个周期的环比,同比数据。需要返回相应日期。
写了一个返回日期的参数供调用。
create function FN_DatePeriod(@date DATETIME,@period int)
RETURNS @tb TABLE(curf DATETIME,curl DATETIME,lasf DATETIME,lasl DATETIME ,lyearf DATETIME ,lyearl DATETIME)
AS
--set @period=5
BEGIN
/*
author:pdy
description:获取输入日期的周期日期
createtime:2016-08-24 15:09:46
@period=1--day
@period=2--week
@period=3--month
@period=4--season
@period=5--year
*/
DECLARE @curf DATETIME,@curl DATETIME,@lasf DATETIME,@lasl DATETIME ,@lyearf DATETIME ,@lyearl DATETIME;
IF @period=1--day
BEGIN
SELECT @curf=Convert(datetime,convert(char(10),@date,120)+' 00:00:00');
SELECT @curl=Convert(datetime,convert(char(10),@date,120)+' 23:59:59');
SELECT @lasf=DATEADD(d,-1,@curf);
SELECT @lasl=DATEADD(d,-1,@curl);
SELECT @lyearf=DATEADD(yyyy,-1,@curf);
SELECT @lyearl=DATEADD(yyyy,-1,@curl);
END
IF @period=2--week
BEGIN
declare @wk int,@lyeard datetime;
select @wk=datepart(dw,@date) ;
if @wk=1
begin
SELECT @curf=Convert(datetime,convert(char(10),DATEadd(d,-6,@date),120)+' 00:00:00');
SELECT @curl=Convert(datetime,convert(char(10),@date)+' 23:59:59');
end
else
begin
SELECT @curf=Convert(datetime,convert(char(10),DATEADD(wk,DATEDIFF(wk,0,@date), 0),120)+' 00:00:00');
SELECT @curl=Convert(datetime,convert(char(10),DATEADD(wk,DATEDIFF(wk,0,@date), 6),120)+' 23:59:59');
end
SELECT @lasf=DATEADD(d,-7,@curf);
SELECT @lasl=DATEADD(d,-7,@curl);
SELECT @lyeard=DATEADD(yyyy,-1,@date);
select @wk=datepart(dw,@lyeard) ;
if @wk=1
begin
SELECT @lyearf=Convert(datetime,convert(char(10),DATEadd(d,-6,@lyeard),120)+' 00:00:00');
SELECT @lyearl=Convert(datetime,convert(char(10),@lyeard)+' 23:59:59');
end
else
begin
SELECT @lyearf=Convert(datetime,convert(char(10),DATEADD(wk,DATEDIFF(wk,0,@lyeard), 0),120)+' 00:00:00');
SELECT @lyearl=Convert(datetime,convert(char(10),DATEADD(wk,DATEDIFF(wk,0,@lyeard), 6),120)+' 23:59:59');
end
END
IF @period=3--month
BEGIN
SELECT @curf=convert(char(10),DATEADD(MM, DATEDIFF(MM,0,@date), 0),120)+' 00:00:00';
SELECT @curl=convert(char(10),DATEADD(d,-1,DATEADD(MM,1,DATEADD(MM, DATEDIFF(MM,0,@date), 0))),120)+' 23:59:59';
SELECT @lasf=DATEADD(MM,-1,Convert(datetime,@curf));
SELECT @lasl=DATEADD(MM,-1,Convert(datetime,@curl));
SELECT @lyearf=DATEADD(yyyy,-1,@curf);
SELECT @lyearl=convert(char(10),DATEADD(d,-1,DATEADD(MM,1,@lyearf)),120)+' 23:59:59';
END
IF @period=4--season
BEGIN
declare @season int;
set @season=(DATEPart(MM,@date)-1)/3;
if @season=0
begin
SELECT @curf=convert(char(4),DATEPart(yyyy,getdate()))+'-01-01 00:00:00';
SELECT @curl=convert(char(4),DATEPart(yyyy,getdate()))+'-03-31 23:59:59';
end
if @season=1
begin
SELECT @curf=convert(char(4),DATEPart(yyyy,getdate()))+'-04-01 00:00:00';
SELECT @curl=convert(char(4),DATEPart(yyyy,getdate()))+'-06-30 23:59:59';
end
if @season=2
begin
SELECT @curf=convert(char(4),DATEPart(yyyy,getdate()))+'-07-01 00:00:00';
SELECT @curl=convert(char(4),DATEPart(yyyy,getdate()))+'-09-30 23:59:59';
end
if @season=3
begin
SELECT @curf=convert(char(4),DATEPart(yyyy,getdate()))+'-10-01 00:00:00';
SELECT @curl=convert(char(4),DATEPart(yyyy,getdate()))+'-12-31 23:59:59';
end
SELECT @lasf=DATEADD(MM,-3,Convert(datetime,@curf));
SELECT @lasl=convert(char(10),DATEADD(d,-1,@curf),120)+' 23:59:59';
SELECT @lyearf=DATEADD(yyyy,-1,@curf);
SELECT @lyearl=DATEADD(yyyy,-1,@curl);
END
IF @period=5--year
BEGIN
SELECT @curf=convert(char(10),DATEADD(yyyy, DATEDIFF(yyyy,0,getdate()), 0),120)+' 00:00:00';
SELECT @curl=convert(char(10),DATEADD(day,-1, DATEADD(yyyy, 1, DATEADD(yyyy, DATEDIFF(yyyy,0,@date), 0))),120)+' 23:59:59';
SELECT @lasf=DATEADD(yyyy,-1,Convert(datetime,@curf));
SELECT @lasl=DATEADD(yyyy,-1,Convert(datetime,@curl));
END
INSERT @tb(curf ,curl ,lasf ,lasl ,lyearf ,lyearl )
select @curf as curf,@curl as curl,@lasf as lasf,@lasl as lasl,@lyearf as lyearf ,@lyearl as lyearl
RETURN;
END