Thursday, 17 January 2008

Navision Data Type and SQL Server Data Type

Variable type in Navision will be mapped to SQL Server data type as follows:
1) Integet --> Integer
2) Option --> Integer
3) Code --> Varchar / Integer / Variant
4) Text --> Varchar
5) Decimal --> Decimal (38,20)
6) Date/Time/DateTime --> DateTime
7) Boolean --> TinyInt
8) Binary --> Varbinary
9) BLOB --> Image
10) DateFormula --> Varchar
11) TableFilter --> Varbinary
12) BigInteger --> Bigint
13) Duration --> BigInt
14) GUID --> UniqueIdentifier
15) RecordID --> Varbinary

Please take note on the Code variable type. Code type in Navision can be created in SQL Server as Varchar, Integer or Variant. The default type is Varchar. You can change the SQL Server Type with the following step:
1) Open the Table in Design mode.
2) Select the Code field that you want to change and the Press Shift + F4 to open the Properties window.
3) Look for SQL Data Type property for the code field and change to the Type that you want.

Another important Data Type to note is the DateTime. All the Date, Time and DateTime in Navision are created as DateTime type in SQL Server because SQL Server has no Date or Time data type. SQL Server only has the DateTime type. Therefore, the Date in Navision will be created as DateTime in SQL Server but only the Date part will be used and the time part will always be stored with 00:00:00:000 for normal date and 23:59:00:000 for closing date.

When we create a Time field in Navision, the field will also be created as DateTime in SQL Server with the Date part always filled with 01-01-1754.

No comments: