SQL server query for time zone

Question

Is there any way to get the current time for particular given time zone?

For EX: GetDateTime("Morocco Standard Time")

It should give the local time of time zone "Morocco Standard Time"

Thanks in Advance, Merlin

Answer 1

I know that the question is quite old, but maybe the answer will help someone.

If you are using SQL Server 2016 you can use new feature AT TIME ZONE. Let's get to the result that you need step by step.

First, use function SYSDATETIMEOFFSET() to retrieve current date and time in UTC time zone. The return type of this function is DATETIMEOFFSET, which stores not only date, but also related time zone information:

SELECT SYSDATETIMEOFFSET()
-- Result is 2016-08-20 13:15:25.3760873 +03:00

Then this with AT TIME ZONE statement. This returns DATETIMEOFFSET in the time zone that you need:

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Morocco Standard Time'
-- Result is 2016-08-20 11:15:25.3760873 +01:00

Additionally if you want to lose time zone information and use DATETIME, you can cast the above result to DATETIME:

SELECT CAST( SYSDATETIMEOFFSET() AT TIME ZONE 'Morocco Standard Time' AS DATETIME)
-- Result is 2016-08-20 11:15:25.376