I have incoming hourly data (5 years worth of data) with timestamps recorded in CST and EST. I want to store this in my database in UTC time. Is there a way for me to convert from CST/CDT/CPT or EST/EDT/EPT to UTC using TSQL?
You can use datetimeoffset (more at [this Stack Overflow thread]) to do the conversion, though you'll need to know coming in which time zone your date represents. Unfortunately, the easiest way to handle a requirement like this is to have the source system send UTC dates. You can use GETUTCDATE() in SQL Server to get the current UTC date, but if the source system can't (or won't) change, it's more complicated to fix on your end. :
One way to account for daylight savings would be to use a calendar table. In this example I use hours but you are much better off using SMALLINT and minutes, since not all timezones are hourly (Newfoundland, for example).