The DateFormula DataType in BC / NAV allows calculates to date to be stored in Business Central. For example “5D” = “5 Days from date X”
These are not easily available in SQL due to how Microsoft stores the data.
Using this, I’ve created the following Function to convert BC/NAV’s DateFormula using SQL. This function allows you to pass in the date, plus the dateformula. This then returns the value using similar function to what is used in Business Central / Dynamics NAV.
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).
select dbo.calcdate(getdate(),'-6D')
