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:
GETUTCDATE()
It is used to view the Date & Time of UTC timezone.
Syntax:
CURRENT_TIMESTAMP
It will display your system event current/present date, time and seconds.
Syntax:
GETDATE ()
It is also used to display your system current/present date, time and seconds.
Syntax: