Convert Local Time From Other Time Zones In SQL Server

Gagan Sharma
9y
19.1k
0
3

Article

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