Sql Server 2008 - How to Convert Gmt(Utc) Datetime to Local Datetime

SQL Server 2008 - How to convert GMT(UTC) datetime to local datetime?

You could do something like this:

declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'

declare @LocalDateTime datetime2 = dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), @InputUtcDateTime)
print @LocalDateTime

or

declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'

declare @LocalDateTime datetime2 = dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), @InputUtcDateTime)
print @LocalDateTime

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

TSQL: How to convert local time to UTC? (SQL Server 2008)

7 years passed and...

actually there's this new SQL Server 2016 feature that does exactly what you need.

It is called AT TIME ZONE and it converts date to a specified time zone considering DST (daylight saving time) changes.

More info here:
https://msdn.microsoft.com/en-us/library/mt612795.aspx

Conversion of UTC time to LOCAL in SQL Query output

First, understand that getdate() returns the local date and time of the server - not of the person running the query. If the server's time zone is set to UTC, then it will indeed return the UTC time.

Since you are running on SQL 2016, and you are asking for the UTC time converted to Pacific time, I suggest you use the built-in AT TIME ZONE statement, as follows:

SELECT [Id]
      ,[BaseSource]
      ,[Source]
      ,[Output]
      ,[SessionId]
      ,[Timestamp]
      ,[Timestamp] AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS LocalTime
FROM [MYDB].[dbo].[SystemOutput]
ORDER BY [Timestamp] DESC

Note, the above assumes that the [Timestamp] field is a datetime or datetime2, and that it is a UTC-based value. The first AT TIME ZONE makes an assertion that the given value is in UTC, resulting in a datetimeoffset. The second AT TIME ZONE then converts from UTC to Pacific time.

If instead the field is already a datetimeoffset type, then the query is even simpler:

SELECT [Id]
      ,[BaseSource]
      ,[Source]
      ,[Output]
      ,[SessionId]
      ,[Timestamp]
      ,[Timestamp] AT TIME ZONE 'Pacific Standard Time' AS LocalTime
FROM [MYDB].[dbo].[SystemOutput]
ORDER BY [Timestamp] DESC

Also, don't be confused by the word "Standard" in the time zone identifier. That value covers both standard time and daylight time, as applicable in the Pacific time zone in the US and Canada.

SQL Server - Convert date field to UTC

If they're all local to you, then here's the offset:

SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime

and you should be able to update all the data using:

UPDATE SomeTable
   SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)

Would that work, or am I missing another angle of this problem?

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.

How can I convert a Sql Server 2008 DateTimeOffset to a DateTime

Converting using almost any style will cause the datetime2 value to be converted to UTC.

Also, conversion from datetime2 to datetimeoffset simply sets the offset at +00:00, per the below, so it is a quick way to convert from Datetimeoffset(offset!=0) to Datetimeoffset(+00:00)

declare @createdon datetimeoffset
set @createdon = '2008-12-19 17:30:09.1234567 +11:00'

select CONVERT(datetime2, @createdon, 1)
--Output: 2008-12-19 06:30:09.12

select convert(datetimeoffset,CONVERT(datetime2, @createdon, 1))
--Output: 2008-12-19 06:30:09.1234567 +00:00

UTC time to Local Time Zone(Central Time) Conversion MS SQL Sever

The linked answer (Sql Server Specify time in another timezone) will get you most of the way there, but to answer the rest of your question, you'll have to make some modifications.

Firstly, I would create a DST calendar, since the DST start and end dates are something that we can compute:

CREATE TABLE dbo.TZCalendar
(
  Year          Int PRIMARY KEY,
  UTC_DST_Start SMALLDATETIME NOT NULL,
  UTC_DST_End   SMALLDATETIME NOT NULL
);

SET DATEFIRST 7;

;WITH cte(d,p) AS 
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-2000+51) DATEADD(YEAR,number,'20000101'),
    CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0
    FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number
)
INSERT dbo.TZCalendar([Year],UTC_DST_Start,UTC_DST_End)
SELECT Year(d),
 -- First Sunday in April (< 2007) or second Sunday in March (>= 2007):
 DATEADD(HOUR, 7, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,2+p,d))+1)%7
    +(7*ABS(p-1)),DATEADD(MONTH,2+p,d))),
 -- Last Sunday in October (< 2007) or first Sunday in November (>= 2007):
 DATEADD(HOUR, 6, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,d))+1)%7
    -(7*p),DATEADD(MONTH,10,d)))
FROM cte
ORDER BY d;

This will generate the DST times from the year 2000 to 2067 (this can be expanded based on your needs).

Next, I would create a function to take a DATE in UTC and return the value in either CST or CDT, depending on the time of the year.

Create Function dbo.fnConvertUTCToCT(@UTC DateTime)
Returns DateTime
As Begin
    Declare @Offset Int = 0

    Select  @Offset = Case When @UTC Between UTC_DST_Start And UTC_DST_End Then -5 Else -6 End
    From    dbo.TZCalendar
    Where   Year = Year(@UTC)

    Set @UTC = DateAdd(Hour, @Offset, @UTC)

    Return @UTC
End

Then you can just call that function with any time specified and get the CST or CDT translation returned:

Select   dbo.fnConvertUTCToCT(GetUTCDate())

2017-09-27 12:24:26.377

---

Related Topics