DateFormula type and MSSQL

I agree with David, dateformulas outside Navision aren't very usable unless you write some code to emulate the way Navision behaves.

Nevertheless, 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.

If you ever would like to update a dateformula value please be careful, you will have to use a cast to remove the "standard" trailing spaces.

like this:

Update [Testcompany$Payment Terms]
set [Due Date Calculation] = CAST('3' + CAST (CAST(5 as varbinary(1)) As char) as varchar(2)) where {condition}

will set the dateformula to '3D' (please note the final cast to varchar(2) the total length of the resulting "string")



Hope this helps!

Hello Zdenal

I remember one post about same question long ago... and I found it by searching... 8)

solution is there: http://www.mibuso.com/forum/viewtopic.php?t=4660

At the end of the post is SQL example how to replace the characters with correct chars.