Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Aydan Golaszewski]

I have a question coming from one of our developers who is trying to read NAV Item Data from a SQL Database.

Does anyone know of a way to read Navision DateFormula fields using Microsoft SQL Server. I have a value of ‘30D’ (or “1W+1D)”  which I can see in the NAV client, but SQL Server returns a value of ‘30’.

We were advised to look at this post from Peter Wijntjes from 2007, has anyone implemented something like this? Your insights are much appreciated.

if you want to be able to read the date formula from SQL this might be of use:

select [Due Date Calculation] ,
cast ([Due Date Calculation]as varbinary) as Binary,
description from [testcompany$Payment Terms]

I have created a number of test entries in de Payment Terms table and the resultset is like this:

Due Date Calculation    Binary            Description
3                       0x3304                          3W
3                       0x3305                          3M
3                       0x3307                          3Y
3+1                   0x33072B3102               3Y+1D
+14+2                0x01052B3134022B3203     CM+14D+2WD

so, appearently all numerical values are stored as there Char representative ( ‘3’ –> 0x33, ‘1’ –> 0x31 )
the ‘+’ sign as well
and the special (language independent elements) are coded:
C = 0x01 *current
D = 0x03
W = 0x04
M = 0x05
Y = 0x07
(and the WD is translated to D) in this example.

——————————
Aydan Golaszewski
Lead ERP Analyst
Jason Pharmaceuticals (Medifast)
Owings Mills MD
——————————

[Loren Raber]

A SQL scalar-valued function seems to work:

CREATE FUNCTION [dbo].[FormatDateFormula]
(
 @DateFormula varchar(32)
)
RETURNS varchar(50)
AS
BEGIN

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

 RETURN @DateFormula

END

Thanks,

——————————
Loren Raber
IT Technician
SUPERB Industries Inc
Sugarcreek OH
————————————————————————-

[Aydan Golaszewski]

Loren thank you so much – that is a gem you provided!  Thanks to Dynamics User Group.

——————————
Aydan Golaszewski
Lead ERP Analyst
Jason Pharmaceuticals (Medifast)
Owings Mills MD
————————————————————————-