2

I want to add my time zone with the function GETUTCDATE() in SQL Server. I searched several times, but did not found any relevant solution. Thanks in advance.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
Tayabur Rahman Masud
  • 21
  • 1
  • 4
  • 1
    Instead of `GETUTCDATE` use [SYSDATETIMEOFFSET()](https://msdn.microsoft.com/en-us/library/bb677334.aspx). It will return a `datetimeoffset` value that include the machine's timezone offset. If you want to switch this to another offset, use [SWITCHOFFSET](https://msdn.microsoft.com/en-us/library/bb677244.aspx) – Panagiotis Kanavos Oct 13 '15 at 12:00

4 Answers4

1

only for sql 2016, it takes into account daylight savings.

CREATE FUNCTION GetBelgiumTime 
(
)
RETURNS datetime2
AS BEGIN  
       declare @dateoffset datetimeoffset
       SET  @dateoffset = convert(VARCHAR(2000),(SELECT GETUTCDATE() AT TIME ZONE  'Central European Standard Time'),126 )


       declare @date datetime2
       set @date = convert(datetime2, LEFT(@dateoffset,28),126)

    set @date = DATEADD(HOUR, convert(int,LEFT(RIGHT(@dateoffset,5), 2)), @date) 
       RETURN @date
END

select  dbo.GetBelgiumTime() as BelgiumDateAndTime
Aflred
  • 4,435
  • 6
  • 30
  • 43
  • 1
    All that conversion to and from varchars and dateadd, is unnecessary. Just use `SYSDATETIMEOFFSET` instead of `GETUTCDATE` and the `AT TIME ZONE` will convert correctly. – Matt Johnson-Pint Nov 02 '16 at 20:04
1

From SQL Server 2016 forward (and Azure SQL DB), you can do this:

SELECT SYSDATETIMEOFFSET() AT TIME ZONE @tz

where @tz is a valid Windows time zone identifier, such as 'Pacific Standard Time', 'Central European Standard Time', etc.

However, if you are on an older version of SQL Server, or prefer to use IANA time zone identifiers, you can use my SQL Server Time Zone Support project to do the following:

SELECT Tzdb.UtcToLocal(GETUTCDATE(), @tz)

where @tz is an IANA standard time zone name, such as 'America/Los_Angeles' or 'Europe/Budapest'.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
0

Use GETDATE() instead GETUTCDATE(). see this link

Masoud
  • 8,020
  • 12
  • 62
  • 123
-1

You can try to use switchoffset like this:

select switchoffset(CAST(myDate as datetimeoffset),'+05:30')  from someTable

Instead of '+05:30' you can specify your timezone value.

If you want to use the timezone with GETUTCDATE() then simply add it like this

select cast(GETUTCDATE() as varchar(20)) + '+5:30'

and if you want to keep it as date only then

select  switchoffset(CAST(GETUTCDATE() as datetimeoffset),'+05:30')
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331