0

I got a legacy system(SQL Server DB) which holds date in plain date time format. There is also a MS Dynamics CRM system which user interacts and inserts data to CRM DB. Data flows from legacy system to CRM.

The problem is CRM thinks all the data coming from legacy system is UTC formatted, in actual its a combination of GMT & BST plain date time values. This results in some transactions time out of phase by an hour.

How should I tackle this problem?

The one solution I can think of is, to identify if the date falls under BST, subtract one hour from it and supply to CRM. As BST = GMT + 1 hr and GMT and UTC are likely the same, thought this might solve the problem.

I'm not sure if I have ruled out all the possible issue with this problem.

Are there any alternative approaches?

Ramu
  • 343
  • 1
  • 6
  • 21
  • There is a possible ambiguity during the last Sunday in October. Given the time 00:30 one can't tell if it's the earlier 00:30, which occurred while BST was in effect, or the later 00:30, which occurred while GMT was in effect, since the clock was turned back one hour at 01:00 BST. Another issue is the dates of changeover have varied, so you will have to construct a table, or use a software library that consults the appropriate time zone database. – Gerard Ashton Sep 25 '18 at 20:38
  • @GerardAshton yes, that possibility exists – Ramu Oct 04 '18 at 08:28

1 Answers1

0

Manipulating the difference & sending the UTC timestamp to CRM works fine.

Alternatively you can incorporate a new UTC field in legacy system & that can be used as offset value, so that sync between two systems.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168