CREATE FUNCTION dbo.CalcDate (@date date, @datefilter nvarchar(32))
RETURNS date

BEGIN

SET @datefilter = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@datefilter,  CHAR(1), 'C'), CHAR(2), 'D'),CHAR(3), 'WD'),CHAR(4), 'W'),CHAR(5), 'M'), CHAR('6'), 'Q'), CHAR('7'), 'Y')


declare @returneddate date = null;

declare @cleanedfilter nvarchar(35) = '';

SET @cleanedfilter = STUFF(REPLACE(REPLACE(CASE
  WHEN LEFT(@datefilter, 1) NOT IN ('+', '-') THEN '+'
  ELSE ''
END + @datefilter
, '+', '|+'
)
, '-', '|-'
)
, 1, 1, ''
) + '|||';


declare @p1 nvarchar(10) = '';
declare @p2 nvarchar(10) = '';
declare @p3 nvarchar(10) = '';


SELECT
  @p1 = LEFT(@cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) - 1)
 ,@p2 = SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, 0) + 1
  , (CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) - 1) - (CHARINDEX('|', @cleanedfilter, 0))
  )
 ,@p3 = REPLACE(SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) + 1
  , 999
  )
  , '|'
  , ''
  );

--Calculate Value

SET @returneddate = (SELECT
    CAST(v3.retp3 AS DATE) 
  FROM (SELECT
      @p1 AS p1
     ,@p2 AS p2
     ,@p3 AS p3) p
  OUTER APPLY (VALUES (
  CASE
    WHEN SUBSTRING(p1, 2, 1) = 'C'                -- <Prefix><Unit>
    THEN CASE SUBSTRING(p1, 3, 2)
        WHEN 'D' THEN @date
        WHEN 'WD' THEN @date
        WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        WHEN 'M' THEN CASE
            WHEN LEFT(p1, 1) = '+' THEN eomonth(@date)
            ELSE DATEADD(DAY, 1, eomonth(@date, -1))
          END
        WHEN 'Q' THEN DATEADD(DAY, CASE
            WHEN LEFT(p1, 1) = '+' THEN -1
            ELSE 0
          END, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0))
        WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        ELSE ''
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 1       -- <Number><Unit>
    THEN CASE
        WHEN RIGHT(p1, 2) = 'WD' THEN DATEADD(DAY, CAST(REPLACE(p1, 'WD', '') AS INT), @date)
        ELSE CASE RIGHT(p1, 1)
            WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(p1, 'D', '') AS INT), @date)
            WHEN 'W' THEN DATEADD(WEEK, CAST(REPLACE(p1, 'W', '') AS INT), @date)
            WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p1, 'M', '') AS INT), @date)
            WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p1, 'Q', '') AS INT), @date)
            WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p1, 'Y', '') AS INT), @date)
          END
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 0       -- <Unit><Number>
    THEN CASE
        WHEN SUBSTRING(p1, 2, 2) = 'WD' THEN DATEADD(DAY, RIGHT(p1, 1) - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) -
          CASE
            WHEN LEFT(p1, 1) = '-' THEN 1
            ELSE 0
          END, 0))
        ELSE CASE SUBSTRING(p1, 2, 1)
            WHEN 'D' THEN DATEADD(DAY, ABS(CAST(REPLACE(p1, 'D', '') AS INT)) - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'D', '') AS INT)) < DAY(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'D', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 0))
            WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(WEEK, ABS(CAST(REPLACE(p1, 'W', '') AS INT)) - 1, datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'W', '') AS INT)) <= DATEPART(WEEK, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'W', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 1, 1))), 0)
            WHEN 'M' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'M', '') AS INT)) <= MONTH(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'M', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ABS(CAST(REPLACE(p1, 'M', '') AS INT)), 1)
            WHEN 'Q' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) <= DATEPART(QUARTER, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'Q', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ((ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) - 1) * 3) + 1, 1)
            WHEN 'Y' THEN datefromparts(ABS(CAST(REPLACE(p1, 'Y', '') AS INT)), 1, 1)
          END
      END
    ELSE ''
  END
  )
  ) AS v1 (retp1)
  OUTER APPLY (VALUES (
  CASE RIGHT(p2, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p2, 'W', ''), 'D', '') AS INT), retp1)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p2, 'W', '') AS INT) * 7, retp1)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p2, 'M', '') AS INT), retp1)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p2, 'Q', '') AS INT), retp1)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p2, 'Y', '') AS INT), retp1)
    ELSE retp1
  END
  )
  ) AS v2 (retp2)
  OUTER APPLY (VALUES (
  CASE RIGHT(p3, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p3, 'W', ''), 'D', '') AS INT), retp2)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p3, 'W', '') AS INT) * 7, retp2)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p3, 'M', '') AS INT), retp2)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p3, 'Q', '') AS INT), retp2)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p3, 'Y', '') AS INT), retp2)
    ELSE retp2
  END
  )
  ) AS v3 (retp3));

return
  @returneddate


END


GO

select dbo.calcdate(getdate(),'-6D')