DateFormula type and MSSQL

Zdenal

Hello all,

I'm creating view in MSSQL and I would like to know how to work with DateFormula fields on MSSQL.
These fields have on MSSQL type varchar(32) so I suppose that e.g. value '30D' which I can see in NAV client, will have the same value in MSSQL, but there is stored something else ('30'). More important is that when I store value '30M', SQL query returns also '30'.
I would like to know how should I work with it or what I'm doing wrong?

Thanks for any advice.

Zdenek

Marije_Brummel

My guess is that is has something to do with the language indepentancy of dateformula's. That is why they got introduced in the first place.

1Y in usa is 1J in holland.

Zdenal

Thank you for your quick reply!

Yes, maybee it has something with language, but what? I have czech version and '30D' in english is translated to czech as '30D'.

(Note: My NAV version is 4.02 Cz and DB collation is: Czech dictionary sort order, code page 1250, case-sensitive, accent-sensitive (83))

Zdenal

So, the answer is that each character used in NAV (like 'D', 'Y', ...) has it's own non printable character stored in SQL (it is needed to view it in HEX). This all is due to launguage independence. Example is from NAV reference guide:
"For example, if a user with language set to ENG (English) enters the date formula "1W+1D" for one week and one day, a user with the language set to FRA (French) will see "1S+1J" and a user with the language set to ESP (Spanish) will see "1S+1D"."

David_Singleton

Zdenal wrote:

Hello all,

I'm creating view in MSSQL and I would like to know how to work with DateFormula fields on MSSQL.
These fields have on MSSQL type varchar(32) so I suppose that e.g. value '30D' which I can see in NAV client, will have the same value in MSSQL, but there is stored something else ('30'). More important is that when I store value '30M', SQL query returns also '30'.
I would like to know how should I work with it or what I'm doing wrong?

Thanks for any advice.

Zdenek

First welcome, great to see more Czechs active, especially in a community dominated by posters in The Czech republic

Anyway, as to DateFormulas, I really think that you are going to have a hard time trying to use them from SQL. Dateformulas are VERY Navision specific, and I really can not see how you would manage them outside of Navision.

You are seeing a simple date formula here, but how would you handle complex ones like CM+14D+2WD (the Tuesday after the second week in the next month). I think in your case you either need a mod to the NAV application to generate the actual dates you need, or create some code with NAS that will give back a Navision date to you that you can read in SQL.

Peter_Wijntjes

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!

David_Singleton

If you want a better understanding of how dates work, take a look at this object (Go-Live Date Calculator) that makes it easy to see how the calcdate function works.

Go-Live with Dynamics Navision the book - base

kine

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.