How do I convert the current time (datetime) in my timezone into another timezone, say France ? I looked around in SO, but did not find any posts which could help me. I am using SQL server 2008.
Asked
Active
Viewed 3.3k times
3
-
2http://stackoverflow.com/questions/16872007/date-time-conversion-from-timezone-to-timezone-in-sql-server – fan711 Oct 27 '13 at 01:21
-
@fan711 - okay, i'll check that and see how it goes. btw, its a sql server 2012 question. – Steam Oct 27 '13 at 01:22
-
Do you have your current Datetime column datatype as Datetimeoffset ??or is it just Datetime ?? – M.Ali Oct 27 '13 at 11:44
-
@MuhammedAli - only datetime. – Steam Oct 28 '13 at 00:15
3 Answers
8
select CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,GetUTCDate()),'+05:30')) Date_India,
select CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,GetUTCDate()),'+03:30')) Date_Iran
Divyang Desai
- 7,483
- 13
- 50
- 76
dardan.g
- 689
- 7
- 17
-
Thanks, but i get the error 'SWITCHOFFSET' is not a recognized built-in function name. on ss2000 and ss2005. – Steam Oct 30 '13 at 20:28
-
query says indian time = 2013-10-30 18:59:51.060. google says 2AM oct 31. – Steam Oct 30 '13 at 20:44
-
1
2
Please replace getdate() with GetUTCDate() to get accurate result otherwise the offset (5:30) in this case will be added to the current time (which will already have some offset) and will result in incorrect result.
For instance if you are in Pakistan then GetDate() will give you time as GMT+5:00 and adding 5:50 offset in this time will result in GMT + 10:30 which is totally wrong.
Umer Khalid Butt
- 115
- 7
rishabh kishore
- 21
- 1
2
I also agree the correct SQL Query is using GetUTCDate() as you see in the screenshot.
select CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,GetUTCDate()),'+05:30')) Date_India_withUtcDate
Jaider
- 14,268
- 5
- 75
- 82