Change DateTime from one Time zone to another Time zone

In SQL Server, Several times we need to convert our DateTime values from one timezone to another timezone so for this we can use SWITCHOFFSET() with datetimeoffset() functions.

In SQL Server 2008, Microsoft has introduced a number of new date and time data types. One of these is the datetimeoffset  data type and SWITCHOFFSET function. Datetimeoffset  data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved in UTC or a particular timezone based on this offset and SWITCHOFFSET returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

Below example demonstrate the conversion of DateTime for India Timezone and Iran Timezone:

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

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

An example to create a user defined Function to handle the conversion between Time zones in SQL server: Handle conversion between time zones in SQL Server

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 and create others columns by using bunch of custom logic to resolve the offset for a particular date.