SQL Server Convert Datetime into Another Timezone

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

Introduce tsql a datetime with a specific timezone and convert it into another one

It seems the challenge you face is that you have IANA time zone identifiers (such as 'America/Los_Angeles') rather than Windows time zone identifiers (such as 'Eastern Standard Time'). For more on this, see the timezone tag wiki in the section titled "Time Zone Databases".

There are a few different ways you can work with IANA identifiers:

• The AT TIME ZONE command works with the time zones of the operating system. Thus when you are running SQL Server on Windows it uses Windows time zone identifiers. If instead you are running SQL Server on Linux then you can use the IANA time zone identifier directly.

  SELECT yourDateTime AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'Europe/Madrid'
  

  Note there are two calls to AT TIME ZONE here. The first asserts the source time zone, creating a datetimeoffset from the datetime (or datetime2) given. The second converts the datetimeoffset to the destination time zone, resulting in a datetimeoffset with the local time and offset adjusted appropriately.

  The remaining approaches below assume you are running on Windows.

• You could write an external application to convert the time zone identifiers from IANA to Windows. You could either update the existing field, or populate a secondary field. For example, if you are writing a .NET Application, then you can use my TimeZoneConverter library to translate. Then you would run the same query as above using the corresponding Windows zones instead. For example:

  SELECT yourDateTime AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'Romance Standard Time'
  

• You could import the CLDR windowsZones.xml file into a table. This file contains most of the translation data between the two different time zone identifier types. You could then join to that table at time of query.

• You could use my SQL Server Time Zone Support package. It's a bit old, but it still works. This imports IANA time zone data into tables in SQL Server, and provides functions for converting. For example:

  SELECT Tzdb.ConvertZone(yourDateTime, 'America/Los_Angeles', 'Europe/Madrid', 1, 1)
  

• You could do all your time zone conversions in an external application. You could use TimeZoneConverter as shown earlier, along with the TimeZoneInfo class in .NET. Or you could use Noda Time instead, with the IANA (aka TZDB) identifiers. There are similar packages in other programming languages as well.

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

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.

Date time conversion from timezone to timezone in sql server

Unix timestamps are integer number of seconds since Jan 1st 1970 UTC.

Assuming you mean you have an integer column in your database with this number, then the time zone of your database server is irrelevant.

First convert the timestamp to a datetime type:

SELECT DATEADD(second, yourTimeStamp, '1970-01-01')

This will be the UTC datetime that corresponds to your timestamp.

Then you need to know how to adjust this value to your target time zone. In much of the world, a single zone can have multiple offsets, due to Daylight Saving Time.

Unfortunately, SQL Server has no ability to work work time zones directly. So if you were, for example, using US Pacific time, you would have no way of knowing if you should subtract 7 hours or 8 hours. Other databases (Oracle, Postgres, MySql, etc.) have built-in ways to handle this, but alas, SQL Server does not. So if you are looking for a general purpose solution, you will need to do one of the following:

• Import time zone data into a table, and maintain that table as time zone rules change. Use that table with a bunch of custom logic to resolve the offset for a particular date.

• Use xp_regread to get at the Windows registry keys that contain time zone data, and again use a bunch of custom logic to resolve the offset for a particular date. Of course, xp_regread is a bad thing to do, requires certain permissions granted, and is not supported or document.

• Write a SQLCLR function that uses the TimeZoneInfo class in .Net. Unfortunately, this requires an "unsafe" SQLCLR assembly, and might cause bad things to happen.

IMHO, none of these approaches are very good, and there is no good solution to doing this directly in SQL. The best solution would be to return the UTC value (either the original integer, or the datetime at UTC) to your calling application code, and do the timezone conversion there instead (with, for example, TimeZoneInfo in .Net or similar mechanisms in other platforms).

HOWEVER - you have lucked out in that Kuwait is (and always has been) in a zone that does not change for Daylight Saving Time. It has always been UTC+03:00. So you can simply add three hours and return the result:

SELECT DATEADD(hour, 3, DATEADD(second, yourTimeStamp, '1970-01-01'))

But do recognize that this is not a general purpose solution that will work in any time zone.

If you wanted, you could return one of the other SQL data types, such as datetimeoffset, but this will only help you reflect that the value is three hours offset to whomever might look at it. It won't make the conversion process any different or better.

---

Updated Answer

I've created a project for supporting time zones in SQL Server. You can install it from here. Then you can simply convert like so:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'Asia/Kuwait')

You can use any time zone from the IANA tz database, including those that use daylight saving time.

You can still use the method I showed above to convert from a unix timestamp. Putting them both together:

SELECT Tzdb.UtcToLocal(DATEADD(second, yourTimeStamp, '1970-01-01'), 'Asia/Kuwait')

---

Updated Again

With SQL Server 2016, there is now built-in support for time zones with the AT TIME ZONE statement. This is also available in Azure SQL Database (v12).

SELECT DATEADD(second, yourTimeStamp, '1970-01-01') AT TIME ZONE 'Arab Standard Time'

More examples in this announcement.

Sql Server Specify time in another timezone

Since the UK timezone can be affected by Daylight Savings Time, I'd suggest building a DST table and using that to create the date.

This article was referenced for this solution: http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/

Create table:

CREATE TABLE dbo.TZCalendar
(
  [Year]        Int PRIMARY KEY,
  UTC_DST_Start SMALLDATETIME NOT NULL,
  UTC_DST_End   SMALLDATETIME NOT NULL,
  ET_DST_Start  AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -4, UTC_DST_Start)),
  ET_DST_End    AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -5, UTC_DST_End))
);

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;

Next, I would create a function to pull the current UK time:

Create Function dbo.fnGetUKTime()
Returns DateTime
As Begin
    Declare @London DateTime = GetUTCDate()

    Declare @Offset Int = 0

    Select  @Offset = Case When @London Between UTC_DST_Start And UTC_DST_End Then 1 Else 0 End
    From    dbo.TZCalendar
    Where   Year = Year(@London)

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

    Return @London
End
Go

Then, you can reference this in your query:

select *
from table
where blah blah
and DatePart(Hour, dbo.fnGetUKTime()) > 14

SQL Server DateTime with timezone

try this query ;

select CONVERT(VARCHAR,GETDATE(),1) + ' ' + RIGHT(CONVERT(VARCHAR,GETDATE(),9), 14)

if you wanted to add timezone try this :

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT

select CONVERT(VARCHAR,GETDATE(),1) + ' ' + RIGHT(CONVERT(VARCHAR,GETDATE(),9), 14) + ' ' + @TimeZone

---

Related Topics