Spent a bit of time debugging a subtle issue with calculating the UTC offset for datetimes stored in a SQL server. The code in question needed to determine the timezone difference between the database server and UTC.
Problematic code that should be avoided when calculating the UTC offset
using (DatabaseDataContext db = new DatabaseDataContext())
{
DateTime current = db.ExecuteQuery("SELECT GETDATE()").First();
DateTime utcCurrent = db.ExecuteQuery("SELECT GETUTCDATE()").First();
TimeSpan differece = utcCurrent - current;
double diff = differece.Hours;
logger.DebugFormat("current:{0}, utcCurrent:{1}, differece:{2}, diff:{3}", current, utcCurrent, differece, diff);
}
This produces the following. Notice the diff toggling between -11 and -12 based on the timing of the calls. DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9970000, diff:-11 DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-12:00:00, diff:-12 DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9830000, diff:-11 DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9900000, diff:-11 DEBUG - current:05/05/2011 11:32:06, utcCurrent:05/04/2011 23:32:06, differece:-11:59:59.9930000, diff:-11
Fixed query to get the offset in a single call
ExecuteQuery("SELECT DATEDIFF(hour,GETDATE(),GETUTCDATE())").First();
Note that this doesn't handle the case where the offset may include partial hours.
See Also:
