We have a SQL database that returns all the times in Greenwich Mean Time (GMT). We are in the Eastern Standard Timezone (EST). This messes up some queries that we have that pull data from specific dates. I tried using the (StartTime AT TIME ZONE 'Eastern Standard Time' as StartTime_ET, but that only returns the result as same in GMT -5. I just want the exact result to be in EST .
This changes the complete logic process of mine. Is there any way to do that?
Assuming your values are all UTC and that your column StartTime is not a datetimeoffset, then you need to turn your value into a datetimeoffset first, and then change the time zone. When you use AT TIMEZONE on a date and time data type that isn't a DATETIMEOFFSET it is assumed that the value is already at the correct timezone. Therefore, for example something like SELECT GETUTCDATE() AT TIME ZONE 'Eastern Standard Time'; would return 2021-08-05 09:53:56.8500000 -04:00 right now, even though the time in EST is actually 2021-08-05 05:53:56.8500000 -04:00 right now.
As a result you need to add the offset first and then use AT TIME ZONE. So, with GETUTCDATE that would be like this:
SELECT SWITCHOFFSET(GETUTCDATE(),0) AT TIME ZONE 'Eastern Standard Time';
Therefore, presumably, you just need to do the same for your column, StartTime, which is also a UTC time:
SWITCHOFFSET(StartTime,0) AT TIME ZONE 'Eastern Standard Time'
If you don't want the timezone portion, then you can convert it back to a different date and time data type:
CONVERT(datetime2(0),SWITCHOFFSET(StartTime,0) AT TIME ZONE 'Eastern Standard Time')