Convert Local Time From Other Time Zones In SQL Server

Firstly, it is for all to understand that the SQL Server functionality provide Time, Dates, Zone details as of UTC. UTC is known as Coordinated Universal Time. This time zone is a Standard Time Zone.

Example: You can check Time in System time watch as it is shown in UTC with hours of difference with time zone:



Now, to resolve or to view the time difference in SQL Server we have options to follow the below given functions:

SWITCHOFFSET

This function is used to find out different time zone timings with your time zone inputs values.

Syntax:

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'Input Value ')

Here, Input Value i.e. +05:00 is as of time hours difference as per UTC time zone. 

Example

DATEADD

It is used to get different time zone timing with your time zone inputs values with getdate().

Syntax:

SELECT DATEADD(hh, timedifferenceUTC ,getdate())

Example: SELECT DATEADD(hh, -10.30, getdate())

Time difference between India and USA is: 10 hours & 30 minutes.

 

Datediff

This function is useful to get date or time differences.

Syntax:

SELECT DATEDIFF(Interval, Date1 , Date2)

 

Example with Datediff:

Select datediff(hh, getutcdate(), getdate()): We use it to get Hours of difference between GMT and IST time

Syntax:

SELECT DATEDIFF(hh, getutcdate(),getdate())

 

Select datediff(minute,getutcdate(),getdate()): We use it to get Minutes of difference between GMT and IST time.

Syntax:

SELECT DATEDIFF(MINUTE, getutcdate(),getdate())

 

SYSDATETIMEOFFSET()

It is used to view current UTC timezone of your system precisely.

Syntax:

Select SYSDATETIMEOFFSET ()

Format : YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]

Example : In the following screenshot you can see system date, time with timezone as +05:30 of IST india 2015-11-20 11:43:41.0688083 +05:30



Sysutcdatetime()

It is used to view the Date & Time of UTC Time zone as per GMT(-5:30 hours). It is more precise than GETUTCDATE

Syntax:

Select Sysutcdatetime()

 Example

GETUTCDATE()
 
It is used to view the Date & Time of UTC timezone.

Syntax:

SELECT GETUTCDATE ()

 Example:

CURRENT_TIMESTAMP

It will display your system event current/present date, time and seconds.

Syntax:

Select CURRENT_TIMESTAMP

 Example: 

GETDATE ()

It is also used to display your system current/present date, time and seconds.

Syntax:

Select GETDATE ()

 Example: