获取相对应的日期周期

工作中需要根据日期返回上一个周期的环比，同比数据。需要返回相应日期。

写了一个返回日期的参数供调用。

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