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:

A1

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:

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

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

Example

A2


DATEADD

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

Syntax:

  1. SELECT DATEADD(hh, timedifferenceUTC ,getdate())

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

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

A3


Datediff

This function is useful to get date or time differences.

Syntax:

  1. 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:

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

A4

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

Syntax:

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

A5


SYSDATETIMEOFFSET()

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

Syntax:

  1. 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

A6

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:

  1. Select Sysutcdatetime()

Example

A7

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

Syntax:

  1. SELECT GETUTCDATE ()

Example:


A8

CURRENT_TIMESTAMP

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

Syntax:

  1. Select CURRENT_TIMESTAMP

Example:

A9


GETDATE ()

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

Syntax:

  1. Select GETDATE ()

Example:

A10