CODEWITHSUNDEEP
sqlt-sqlsql-server-2008timezone
Amit Patil
Amit Patil

Reputation: 1993

Timing compare in SQL Server 2008

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

Upvotes: 5

Views: 452

Answers (3)

Florian Reischl
Florian Reischl

Reputation: 3856

Have a look at SYSUTCDATETIME():

SELECT SYSDATETIME(), SYSUTCDATETIME();

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

Upvotes: 2

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131364

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')

Upvotes: 2

abatishchev
abatishchev

Reputation: 100268

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)

Upvotes: 0

Related Questions