Timing compare in SQL Server 2008

Question

Hi All.

How to compare Indian time with world's other country times in SQL Server 2008?

Means i want to know if in India its mid night 1 am what will be timing in other countries through SQL Server 2008

Answer 1

DECLARE @indianTime DATETIME = GETDATE()
DECLARE @timeZoneOffset SMALLINT = 3 -- or -3

SELECT DATEADD(hour, @timeZoneOffset, @indianTime)

See next MSDN articles: 1, 2

---

But it's better to use SQL Server 2008 and DateTimeOffset type which supports time zones.

So you could do next:

DECLARE @indianTime DATETIME = SYSDATETIMEOFFSET()
DECLARE @timeZoneOffset NVARCHAR(6) = '-03:00'

SELECT SWITCHOFFSET(@indianTime, @timeZoneOffset)

Answer 2

Have a look at SYSUTCDATETIME():

SELECT SYSDATETIME(), SYSUTCDATETIME();

With UTC Time you can easily calculate the time in any other country.

Greets Flo

Answer 3

SQL Server 2008 uses the DateTimeOffset type to represent DateTime with a timezone offset.

You can convert values from one timezone to another using the SWITCHOFFSET function.

To create DateTimeOffset values for a specific timezone from existing date values, use TODATETIMEOFFSET.

You can get the current date and time including the offset using SYSDATETIMEOFFSET.

Finally, you can use plain-old DATEDIFF to compare DateTimeOffset values and get their difference.

To get your current time in India and switch it to another timezone you have to know the proper time offset. The statement would be something like

DECLARE @localTime DATETIMEOFFSET=SYSDATETIMEOFFSET()
SELECT @localTime, SWITCHOFFSET(@localTime,'-5:00')