Data Format Considerations
In Microsoft Dynamics NAV 2018, data formats impact the way your data is compared and sorted
Code Fields
In Microsoft Dynamics NAV 2018 code fields can be represented by several SQL Server data types. Code fields have a property, SQL Data Type, which determines whether they contain integers, text strings, or a mixture of both.
To set code field properties
On the Tools, choose Object Designer.
Choose Table and select the appropriate table.
Choose Design.
Select the field whose data type is defined as code, choose View, and then choose Properties.
You can set the SQL Data Type property to Varchar, Integer, or Variant. Leaving the value as Undefined is the same as selecting Varchar, which is the default value.
Note
When you set the SQL Data Type property to Varchar, the SQL Server data type that is used is NVARCHAR.
When you create a table in Microsoft Dynamics NAV 2018, the code field data is stored in NVARCHAR, INTEGER, or SQL_VARIANT columns in the SQL Server table that correspond to the SQL Data Type property’s values Varchar, Integer, or Variant.
Varchar
When you set the value of the SQL Data Type property of a code field to Varchar, all the values in the field are compared and sorted as character data, including numeric values.
Integer
When you set the value of the SQL Data Type property of a code field to Integer:
All the values in the field are compared and sorted as integers. No alphanumeric values can be stored in the field.
If you enter negative values in the column outside Microsoft Dynamics NAV using external tools, they cannot be read into Microsoft Dynamics NAV.
The value "0" (zero) is used to represent an empty string in Microsoft Dynamics NAV.
Nonnumeric code values or any numeric values beginning with "0" (zero) cannot be entered in the code field.
Variant
When you set the value of the SQL Data Type property of a code field to Variant:
The values in the field are compared and sorted according to their base data type. Numeric values are sorted after alphanumeric values.
Data that is entered into the code field in Microsoft Dynamics NAV is stored as either the NVARCHAR or INTEGER base data type, depending on the value that has been entered.
Any value beginning with "0" (zero) can be entered in the code field and is stored as an INTEGER base data type.
Note
Be aware that the Variant data type is not supported by all the third-party tools that can be used to access data in SQL Server databases.
Data and Time Fields
SQL Server stores information about both date and time in columns of the DATETIME and SMALLDATETIME types. For date fields, Microsoft Dynamics NAV uses only the date part and places a constant value for the time. For a normal date, this contains 00:00:00:000. For a closing date, it contains 23:59:59:000 for a DATETIME and 23:59:00:000 for a SMALLDATETIME.
The Microsoft Dynamics NAV undefined date is represented by the earliest valid date in SQL Server: 01-01-1753 00:00:00:000 for a DATETIME, and 01-01-1900 00:00:00:000 for a SMALLDATETIME.
For time fields, only a SQL Server DATETIME type can be used. Microsoft Dynamics NAV uses only the time and places a constant value for the date: 01-01-1754. The Microsoft Dynamics NAV undefined time is represented by the same value as an undefined date.
In order for Microsoft Dynamics NAV to interpret date and time values correctly, the formats mentioned earlier must be used when linking Microsoft Dynamics NAV table definitions to external tables or views. For more information, see Using Linked Objects.
To reformat a DATETIME or SMALLDATETIME column that is to be used as a date field in Microsoft Dynamics NAV, an UPDATE statement can be applied to the table data. The following is an example of such an update statement.
UPDATE [My Table] SET [My Date] = CONVERT(CHAR(10), [My Date], 120)
For a closing date, a CONVERT style of 120 can be used to set the appropriate time part. To reformat a time field, a similar statement can be used.
UPDATE [My Table] SET [My Time] = CAST('1754-01-01 '+CONVERT(CHAR(8), [My Time], 108) AS DATETIME)
As an alternative to modifying the table data, you can create a view that applies the necessary conversion to the column and gives the column an alias. However, you cannot update views that are created in this way and it is more efficient to change the data than to apply conversions for every row.
Note
The information about date and time fields only applies to fields of the Date data type and Time data type; it does not apply to fields of the DateTime data type.