Time Zone Conversion in SQL Query

Convert Datetime column from UTC to local time in select statement

You can do this as follows on SQL Server 2008 or greater:

SELECT CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    MyTable.UtcColumn), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM MyTable

You can also do the less verbose:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
       AS ColumnInLocalTime
FROM MyTable

Whatever you do, do not use - to subtract dates, because the operation is not atomic, and you will on occasion get indeterminate results due to race conditions between the system datetime and the local datetime being checked at different times (i.e., non-atomically).

Please note that this answer does not take DST into account. If you want to include a DST adjustment, please also see the following SO question:

How to create Daylight Savings time Start and End function in SQL Server

Azure SQL - Convert time zone to 'Central Standard Time'?

Azure SQL database always follows UTC. Use AT TIME ZONE in Azure SQL Database if you need to convert date and time information in a non-UTC time zone.

AT TIME ZONE converts input date to target time zone. It returns datetimeoffset value in the target time zone.

Query:

declare @current_cst datetimeoffset;
set @current_cst = (SELECT getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time')

declare @current_utc datetimeoffset;
set @current_utc = getutcdate();

--retunrs datetimeoffset format
select @current_utc current_utc, @current_cst current_cst 

--retunrs 102 format(yyyy.mm.dd)
select convert(varchar(10), @current_utc, 102) as current_utc, convert(varchar(10), @current_cst, 102) as current_cst 

Note: A list of installed time zones are available in sys.time_zone_info view.

SQL Server how to avoid Time Zone conversion in WHERE clause

A different approach is to change your input to UTC. That is a one time operation and easier for SQL to filter on. Below are different ways to achieve same goal depending on your sql version.

-- 1
WHERE T.start_date_utc
     BETWEEN '1/1/2018 08:00:00 AM' /*PST to UTC*/
        AND '2/28/2018 07:59:59 PM' /*PST to UTC*/
-- 2    
WHERE T.start_date_utc
 BETWEEN DATEADD(HOUR, 8, '1/1/2018 00:00:00 AM'),  /*PST to UTC*/
    AND DATEADD(HOUR, 8, '2/28/2018 23:59:59 PM' /*PST to UTC*/

-- 3
WHERE T.start_date_utc
     BETWEEN SWITCHOFFSET('1/1/2018 00:00:00 AM', '-08:00') /*PST to UTC*/
        AND SWITCHOFFSET('2/28/2018 23:59:59 PM', '-08:00') /*PST to UTC*/

/*Avoiding hard coded values*/

-- 4
DECLARE @offset INT

 SELECT @offset = DATEPART(TZOFFSET, CONVERT(datetime,'1/1/2018 00:00:00 AM') AT TIME ZONE 'Pacific Standard Time') * -1

 WHERE T.start_date_utc
 BETWEEN DATEADD(MINUTE, @offset, '1/1/2018 00:00:00 AM'),  /*PST to UTC*/
    AND DATEADD(MINUTE, @offset, '2/28/2018 23:59:59 PM' /*PST to UTC*/

-- 5        
WHERE T.start_date_utc
     BETWEEN CONVERT(datetime,'1/1/2018 00:00:00 AM')  AT TIME ZONE 'Pacific Standard Time' /*PST to UTC*/
        AND CONVERT(datetime,'2/28/2018 23:59:59 PM')  AT TIME ZONE 'Pacific Standard Time' /*PST to UTC*/

sql server convert datetime into another timezone?

 select CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,GetUTCDate()),'+05:30')) Date_India,

 select CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,GetUTCDate()),'+03:30')) Date_Iran

check here

Time zone conversion in SQL query

You can use this query, without having to worry about timezone changes.

select to_char(cast(application_recv_date as timestamp) at time zone 'US/Eastern',
               'MON dd, YYYY'
              )
from application;

Ex:

EDT:

select cast(date'2014-04-08' as timestamp) d1,
       cast(date'2014-04-08' as timestamp) at time zone 'US/Eastern' d2
from dual;

D1                                 D2
---------------------------------- -------------------------------------------
08-APR-14 12.00.00.000000 AM       07-APR-14 08.00.00.000000 PM US/EASTERN

EST:

select cast(date'2014-12-08' as timestamp) d1,
       cast(date'2014-12-08' as timestamp) at time zone 'US/Eastern' d2
from dual;

D1                                 D2
---------------------------------- -------------------------------------------
08-DEC-14 12.00.00.000000 AM       07-DEC-14 07.00.00.000000 PM US/EASTERN

UPDATE:

Thanks to Alex Poole for reminding that, when timezone is not specified, local timezone is used for conversion.

To force the date to be recognized as GMT, use from_tz.

from_tz(cast(date'2014-12-08' as timestamp), 'GMT') at time zone 'US/Eastern'

How to assume a DATETIME is in my local time zone, then convert to UTC

AT TIME ZONE is one half of the equation. The other is getting the machine's time zone, which even 2017 has no actual function or SERVERPROPERTY for -- you need to poke around in the registry. From SQL Server 2019 onwards (and Azure SQL), there's the CURRENT_TIMEZONE() function, but (in a seemingly bizarre oversight) the return value of this cannot be used as the input to an AT TIME ZONE clause or correlated with sys.time_zone_info, so it's not clear what purpose that's supposed to serve.

DECLARE @TimeZone NVARCHAR(4000);
EXEC master.dbo.xp_regread 
    'HKEY_LOCAL_MACHINE', 
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 
    'TimeZoneKeyName', 
    @TimeZone OUT;

-- For me, that's 'W. Europe Standard Time'

SELECT {ts '2019-03-31 02:00:00' } AT TIME ZONE @TimeZone AT TIME ZONE 'UTC'
-- Yields '2019-03-31 01:00:00.000 +00:00', DST was not in effect

SELECT {ts '2019-03-31 03:00:00' } AT TIME ZONE @TimeZone AT TIME ZONE 'UTC'
-- *Also* yields '2019-03-31 01:00:00.000 +00:00', DST was in effect

These examples illustrate the peril of having to convert time stamps that weren't recorded with actual offsets: in the "twilight zone" of DST turnover, you get times that are inherently ambiguous. The results of such adjustments are only mostly correct, skipping and duplicating an hour twice a year. Some data sets can live with that, some can't.

---

Related Topics