Convert Local Time From Other Time Zones In SQL Server

Gagan Sharma

function in SQLConvert Local Time in SQL ServerSQL Server Time Zones

12 Comments

Join the conversation! Your thoughts help the community grow.

VENKATESWARULU S7y ago
Hi I have different Time Zones to convert IST Time zone But this different time zones are in excel here every 3month time is change how to do differents is this any Ui changes possible
Subash10y ago
Good one
Santhakumar Munuswamy11y ago
Nice share
Debasis Saha11y ago
Nice one..
Harshad Pansuriya11y ago
Nice one
Sibeesh Venu11y ago
Nice Share
Banketeshvar Narayan11y ago
Good One
Mukesh Kumar11y ago
Good Start...Continue
Humayun Kabir Mamun11y ago
Nice...
Yashwant Vishwakarma11y ago
Nice info dude !!
Upendra Pratap Shahi11y ago
nice share....good start...keep it up...
Raja T11y ago
Nice one, Thanks for sharing

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)

A11

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