获取相对应的日期周期

我会带着你远行 2022-07-17 00:48 241阅读 0赞

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

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

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