0

I have an SP with a bunch of queries that use datetime like Getdate().

Is there a way that I can set a global flag at the beginning of the SP to set a timezone offset for the datetimes?

I need something like the set dateformat dmy, but to offset the timezone.

Varada
  • 16,026
  • 13
  • 48
  • 69
user1939553
  • 79
  • 1
  • 8
  • As soon as timezones enter the picture, it's almost always more sensible to just work with UTC in the database. Is that not an option for you? – Damien_The_Unbeliever Jan 15 '13 at 11:31
  • @Damien_The_Unbeliever Sure the time is in UTC, but I need to offset it for reporting purposes – user1939553 Jan 15 '13 at 12:18
  • What are you expecting from this global flag? What effect should it have on your data? Can you provide one or two code fragments detailing your requirements? – Serg Jan 15 '13 at 15:25
  • If you're alright with the TZ-offset of the server and not dealing with past / future dates, you may have a look at [my question](http://stackoverflow.com/questions/14194439/utc-datetime-offset) - There is a regread-function to get tz-info. But it refers to SQL-server-setting. – Nico Jan 15 '13 at 18:06
  • I recently solved a bunch of T-sql timezone issues with https://tsqltoolbox.codeplex.com/ – Niels Bosma Feb 19 '16 at 09:17

1 Answers1

0

There is no magical global offset variable that will change the time reported by SQL Server for a a session. If you think about it, you'll notice that variables declared in a procedure never affect the server. You'd need something in, say, the SET command to do that.

I assume the application provides the offset, based on the TZ database or equivalent, and you just want to apply it as easily as possible. What you could do is wrap getdate() with a new function localedate() that accepts as input the offset and applies it to getdate() with dateadd().

Bear in mind that dates saved in the database have no associated time zone. Localizing them would be a bigger undertaking.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31