0

Lets say I have a table called Test. Test has a Column called CreatedDate. This is the DateTimeOffset at the time of creation of the row. Test also has a Column called ExternalDate. This is an externally provided time through an API.

What I require to do is to calculate the difference between CreatedDate and ExternalDate. CreatedDate is DateTimeOffset, but ExternalDate is always provided as DateTime2. The external system providing this time does not provide offset or timezone data.

So we can see here, that the calculation can be off if we are in DST or not by an hour.

We are using SQL2008 unfortunately.

I am thinking of creating a table of DST dates, and performing a join when migrating the dating to figure out this issue as mentioned in another thread here. So historical migration is ok. (Other Thread: Migrating SQL stored DateTime values to DateTimeOffset best practice?)

Question is, I will continue to have the external system send in DateTime2 with no offset value. I am worried if I do the calculation on the fly with a join of this same DST table there may be performance implications? I am not too familiar with SQL performance. What does a SQL expert out there think about this? or is there some other more efficient way to do this on the fly?

Thanks! Much appreciated!

gti303
  • 65
  • 2
  • 6
  • Hint: extract timezone from CreatedDate, adjust ExternalDate to timezone, Diff the dates. – Mitch Wheat Dec 09 '17 at 00:57
  • You say the external system is not providing offset or time zone data, but do you *know* what time zone that data is supposed it be in? If you do not - then you'll have no way to solve this problem. One cannot assume the time zone on the server is the appropriate context for the values being externally received. – Matt Johnson-Pint Dec 21 '17 at 20:55

0 Answers0