UTC troubles

rocatis

I have a problem fetching datetimes from a SQL table on a server separate to the one used by NAV. Through a series of views I've managed to create a table in NAV which looks directly into the desired table on the separate SQL Server.

Testing my views step by step I can validate that the datetime fields are passed along just fine. The time part corresponds to the value originally created by the source application and maintains this value regardless of Regional Settings.

Then NAV comes along and f*cks everything up. Apparently NAV perceives the datetime field in SQL as UTC (same as GMT) and proceeds to adjust it according to the time zone and daylights settings defined under Regional Settings. This means that unless you happen to live in a UTC/GMT zone or set the Regional Settings of your pc to such a location, your datetime in NAV will be wrong.

This is a pretty severe error and as far as I can see there's no workaround for it.

You could alter the SQL View to shift the datetime opposite to what NAV is going to do (SQL can give you the difference between local time and UTC). That would be a solution if you only have clients in one timezone, except it isn't. Consider two pc's standing right next to each other. They both show the correct time. But opening the table/view in NAV they show different time stamps for the same record.

Why? Because one pc is set up to use daylight savings while the other is not (the time has been manually set).

You could argue that this erroneous setup of one of the pc's but even if you stress that the Regional Settings should be the same for all workstations this particular problem is going to boomerang you sooner or later. Add insult to injury, if you use the fetched data to update "normal" datetime fields in NAV, you're screwed.

Any thoughts on this? Please tell me I'm just a SQL n00b. :?

As far as I can see we should have a property for datetime fields in LinkedObjects specifying whether the source field should be treated as an UTC date or not. But then again, I'm a bit of a dreamer...

pdj

It doesn't help you much, but I will join the choir complaining about the DateTime fieldtype. It is also a problem for tables not linked. I am recommending people to use Date and Time wherever possible, and only use DateTime for calculations...

dmccrae

If you only need to read the field from the view you could split it into 2 columns, that construct a valid NAV Date and Time fields by concatenating the correct date and time parts respectively. Then you could map them to NAV Date and Time types, which means they will be treated as Local values, not UTC, and will not undergo any conversion by NAV.

This won't work if you also need to modify the field though.