Copying DateTime fields from one database to another

rsaritzky

Hi,

I have some ADO code that copies records from one NAV database to another NAV database. There are some DateTime fields in these records.

The query does a SQL FORMAT to return the DateTime in the format where it can be EVALUATE'd into the target table:

SELECT [No_], FORMAT([Complete Date_Time], 'MM/dd/yyyy hh:mm:ss tt') AS [Complete Date_Time]
FROM [Ticket] WHERE [No_] = 'xxxxxx'

The issue is the often-discussed issue that in SQL, NAV DateTimes are stored as the UTC datetime.

So if I look at a date in NAV that says, for example '10/30/14 07:56PM', when I use the above query, the result of the DateTime field is
'10/31/14 02:56AM'.

I've found some sql code that will convert a DateTime to local time, but doesn't account for Daylight Savings time. So, for example, if I change the query above to use the following formula for the DateTime field to be:

DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), [Complete Date_Time])

The result is then 10/30/14 6:56PM - 1 hour off.

So, has anyone figured this out? I could put in a check for DST, but then I would want to subtract either 8 hours or 7 hours from the DateTime returned by the SQL Query (depending on DST, which I can figure out).

Can you do date arithmetic on a Datetime field, like below? (8 hours is 28800000 milleseconds)

"Complete Date Time" := "Complete Date Time" + 28800000;

Any other ideas?

Thx

SonGoten13

Did you try to do the formating in NAV?
I recently hat to read some Datetime from another SQL-Database an the Code i use looks like this.

// SQL Format: YYYY-MM-DD HH:MM:SS:000
LO_TE_DateTime :=
  COPYSTR(TE_SQLDateTime,9,2)+'.'+
  COPYSTR(TE_SQLDateTime,6,2)+'.'+
  COPYSTR(TE_SQLDateTime,1,4)+' '+
  COPYSTR(TE_SQLDateTime,12);

EVALUATE(LO_DT_DateTime,LO_TE_DateTime);

rsaritzky

Thanks for the suggestion. Your idea won't work because the DateTime field in the source database is already converted to UTC, so the "real" DateTime has been "lost" unless there is a way in C/AL to retrieve the UTC, convert it to the correct "displayed" time, and store it back into the table.

Your suggestion, however, gave me an idea for a workaround. In the Source table, I decided to save a copy of the Date/Time in a Text field. When I copy it to the target table, I then just EVALUATE it into the target's Date/time field.

Thanks for the idea.