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

mplatvoet · 2005-01-05

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.

RobertMo · 2005-01-06

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.

RobertMo · 2005-01-06

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.
  }
}

mplatvoet · 2005-01-06

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/

RobertMo · 2005-01-06

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!

xavigepe · 2006-02-20

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?

Louis · 2006-04-25

Native DB - How to display UTC ?

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

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

Comments

Categories