Hi,
Best wishes for the new year!
I have a simple question:
Navision stores a datetime field in the database as UTC time and converts this to the actual time zone datetime on the client. Very beautiful!
But I need datetime to be stored in the SQL database as the actual (=local) datetime (SQL Server does this normally when Navision is not the application) for need of dataexchange with DTS with another application.
Does anyone know a simple way to achieve this (either in Navision or DTS/VBScript/TRansactSQL?) or a simple way to convert the UTC time to the local time?
Please take in consideration that we have summer and wintertime in the Netherlands (so simple subtraction of addition of one hour won't do!).
Thanks in advance!
Mike.
0
Comments
We designed special "interface" tables in Navision, where all datetimes were acttualy defined as Text30 Datatypes.
We agreed to use SQL internal datetime format (YYYY-MM-DD HH:MM:SS.TTT) in this text fields. We have also written 3 functions:
CreateDateTimeString ExtractDateOutOfString ExtractTimeOutOfString
If you want I can post them.¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Here is CU 50000 in txt. Copy and paste in Notepad, save as txt and import in Navision.
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
When I saw your first reply I thought it would be the simpelest way to store the datetime in text fields instead of datetime fields... but thanks for your code! Maybe I will use the way you solved this issue! \:D/
By using suggeseted functions you have complete control in your hands. And since it's SQL native format, it's very simple to convert it within some SQL procedures.
And last but not least, the sorting is correct = same as DateTime!
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
12H16 in Belgium => UTC 11H16 in London
or 16H16 in Asia/Katmandu
or 10H16 in Africa/Abidjan
How to capture and how to display UTC ?
Suppose an entry at the "same time" in these locations :
- Brussels, - London, - Katmandu and - Abidjan.
I need to show the UTC of the four entries in Brussels.
Any idea ?
Louis