SQL Server 2005 - NAVISION TIME DATA TYPE

b2b_Vijay

Hi All,

I am trying to insert Start Time and End Time which has Data Type as Time from Navision and when i open SQl Server 2005 Management Studio and try to view the records, it is stored as 1/01/1754 9:00:00 AM and 1/01/1754 12:00:00 AM respectively. If i try to change the year in SQL and open the form in Navision, the system throws a error message as below -

---

Microsoft Business Solutions-Navision

---

The Pay Time Sheet Journal Line table contains a Time field that uses invalid data for the date component of the DATETIME value:

Field: End Time
Value: 1854-01-01 00:00:00.000

---

OK

---

I see in SQL that there is no Time Datatype. SQL uses DateTime Datatype.
SQL Version
============
[Start Time] datetime Unchecked
[End Time] datetime Unchecked

Navision Version
=================================
Enabled Field No. Field Name Data Type
Yes 89 Start Time Time
Yes 90 End Time Time
==================================

Do we need to Hard code the date 1/01/1754 before the time, when inserting into SQL database through external interface and not from Navision.As if i use the Current date to insert along with the time, Navision form throws an error and does not open

Any advice would be highly appreciated.
Thanks

kriki

SQL does NOT have a field "Time" or "Date". So both Navision datatypes are converted to SQL datetime with a certain structure (for time it is "1/01/1754" + the time). If you don't follow this structure you have a problem.
So it is NECESSARY to put "1/01/1754" + time in the SQL datetime field.
Another possibility is that in Navision you have a datetime-field instead of a date and a time field. In this case there shouldn't be a problem.

bbrown

If you choose to use DateTime fields be aware that the client timezone settings will impact the value displayed.

From Help:

The biannual change to and from daylight saving time affects the way in which datetimes are displayed. For example, if you are located in central Europe, you are in the central European time zone and are using Central European Time (CET). During the winter the difference between CET and UTC is 1 hour. When you change to daylight saving time for the summer the difference is 2 hours.

This means that the local time representation of a UTC will vary depending on the time zone you are located in and whether you are using daylight saving time or not. Therefore, any datetimes that were entered during the winter will be converted and displayed differently after you have changed to daylight saving time for the summer. In other words, datetimes will not necessarily be displayed the same as when they were entered.