Ran into the most curious issue after migrating live sites running SQL 2008R R2 into Amazon AWS virtual instances.  There was a job scheduler module that populated a queue table based on triggers from other tables.

The triggers used the T-SQL GETDATE() function to log the current datetime of the transaction being triggered.  However, after migrating SQL to a virtual instance, the “current” datetime was off.     The trigger code was seeing the wrong datetime value every time GETDATE() was called.

I even went so far as to light up a local SQL install and compare it to the instance SQL install.  Executing SELECT GETDATE() on separate copies of SQL doesn’t report the same values.  Even though both server OS environments have the same date and time zone settings.

Finally I found another blog post about Azure but discussed the same problem.  Apparently when SQL is run in a virtual instance, it ALWAYS reports GETDATE() in UTC format regardless of the server OS time zone settings.

The IIS instance didn’t seem to have any trouble with it.  The issue was specific to the SQL OS.

One more reason (and lesson) why it’s better to store your SQL datetime values in UTC format so things like this don’t happen in the first place……