How to store local time of datetime field in SQL Server?

mplatvoetmplatvoet Member Posts: 34
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.

Comments

  • RobertMoRobertMo Member Posts: 484
    We had the same problem when we were interfacing Navision SQL DB with other SQL/Oracle Databases.

    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.
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • RobertMoRobertMo Member Posts: 484
    Well, what the hack.
    Here is CU 50000 in txt. Copy and paste in Notepad, save as txt and import in Navision.
    OBJECT Codeunit 50000 DateTime 2 String Conversion
    {
      OBJECT-PROPERTIES
      {
        Date=06.01.05;
        Time=17:03:13;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        OnRun=BEGIN
                MESSAGE(CreateDateTimeString(TODAY,TIME));
                MESSAGE('%1',ExtractDateOutOfString(CreateDateTimeString(TODAY,TIME)));
                MESSAGE('%1',ExtractTimeOutOfString(CreateDateTimeString(TODAY,TIME)));
              END;
    
      }
      CODE
      {
    
        PROCEDURE CreateDateTimeString@1000000000(ldtDate@1000000000 : Date;ltmTime@1000000001 : Time) ltDateTimeString : Text[30];
        VAR
          ldtmDateTime@1000000002 : DateTime;
        BEGIN
          // CreateDateTimeString
          // Uncomment following lines if you want to round the seconds
          //ldtmDateTime := CREATEDATETIME(ldtDate, ltmTime);
          //ldtmDateTime := ROUNDDATETIME(ldtmDateTime);
          //ltmTime := DT2TIME(ldtmDateTime);
    
          // Outgoing Format: YYYY-MM-DD HH:MM:SS.TTT
          ltDateTimeString :=
             FORMAT(ldtDate,0,'<Year4,4>-<Month,2>-<Day,2>')
             + ' ' +
             FORMAT(ltmTime,0,'<Hours24,2><Filler Character,0>:<Minutes,2>:<Seconds,2>.<Thousands,3><Filler Character,0>');
          ltDateTimeString := DELCHR(ltDateTimeString, '<>',' ');
        END;
    
        PROCEDURE ExtractDateOutOfString@1000000001(ltDateTimeString@1000000000 : Text[30]) ldtDate : Date;
        VAR
          iDay@1000000002 : Integer;
          iMonth@1000000003 : Integer;
          iYear@1000000004 : Integer;
        BEGIN
          // ExtractDateOutOfString
          // Incoming Format: YYYY-MM-DD HH:MM:SS.TTT or YYYY-MM-DD
          IF ltDateTimeString = '' THEN EXIT(0D);
          EVALUATE(iDay,COPYSTR(ltDateTimeString,9,2));
          EVALUATE(iMonth,COPYSTR(ltDateTimeString,6,2));
          EVALUATE(iYear,COPYSTR(ltDateTimeString,1,4));
          ldtDate := DMY2DATE(iDay,iMonth,iYear);
        END;
    
        PROCEDURE ExtractTimeOutOfString@1000000002(ltDateTimeString@1000000000 : Text[30]) ltmTime : Time;
        VAR
          iHour@1000000001 : Integer;
          iMin@1000000002 : Integer;
          iSec@1000000003 : Integer;
          ltTemp@1000000004 : Text[50];
        BEGIN
          // ExtractTimeOutOfString
          // Incoming Format: YYYY-MM-DD HH:MM:SS.TTT or YYYY-MM-DD
          IF ltDateTimeString = '' THEN EXIT(0T);
          ltTemp := COPYSTR(ltDateTimeString,STRPOS(ltDateTimeString,' '),STRLEN(ltDateTimeString));
          EVALUATE(ltmTime,ltTemp);
        END;
    
        BEGIN
        END.
      }
    }
    
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • mplatvoetmplatvoet Member Posts: 34
    Thanks!

    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/
  • RobertMoRobertMo Member Posts: 484
    mplatvoet wrote:
    ... the simpelest way to store the datetime in text fields instead of datetime fields...
    Yes, but simple MyTxt:= Format(MyDateTime) might be dependent again on OS Regional Settings, etc.
    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!
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • xavigepexavigepe Member Posts: 185
    OK, so datetime fields are stored in UTC format. If I want to store my local time here in Barcelona in a datetimefield, do I always have to add one hour to the time stored in the field or it depends if we are in daylight saving time?
  • LouisLouis Member Posts: 78
    Native DB - How to display UTC ?

    :lol:

    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
Sign In or Register to comment.