Jo Douglass shows how to use the DATETIMEOFFSET data type and AT TIME ZONE syntax to convert between UTC and local times:
select SysDateTimeOffset();and you should see a date and time which mirrors your server’s current time, plus a time zone offset showing its current offset from UTC; this includes any time zone offset, plus any daylight savings time offset.
If I were to run this (from the UK) on August 15th, 2017 while my clock is showing that it’s noon exactly, I would get
2017-08-15 12:00:00.0000000 +01:00; the +01:00 offset is because the UK is offset by one hour from UTC during daylight savings. The
datetime2portion of a
datetimeoffsetis in local time, not UTC.
My normal operation is to store everything in UTC and let the application convert to local times. That allows you to compare dates much more easily and reduces confusion around daylight savings time.