These are not easily available in SQL due to how Microsoft stores the data.
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
This function can be used as follows (today is the 9th January).