SQL Server - Get time (daylight savings aware) from another time zone

I'm looking for a way in SQL Server without using the .NET framework to find out the time in a given time zone, paying attention to daylight savings. However, this method also needs to take account for states (e.g. Arizona) that do NOT follow DST.

There is a workaround if the server is located somewhere that does follow DST - subtract your current time from GMT to get the offset - but I am looking fo开发者_开发百科r a more general-purpose solution. I know the .NET framework has ways to do this, but it would be great to avoid having this kind of dependency within this stored procedure.

Thanks!

You could do this in a CLR Stored Procedure. This still is a process from within SQL Server, and is a dependency on the server itself, not any external .Net app that you have to write.

http://msdn.microsoft.com/en-us/library/ms190790.aspx

I typically consider local time stuff to be a view concern, and therefore put it into the web/desktop application. In the database, I work entirely in UTC. So all of the datetime columns in the DB have UTC stored in them, all of the sprocs for reporting and such take UTC, etc. The web/desktop app convert any dates going to/from the user into whatever timezone they have selected.

I know that doesn't really answer your question, but maybe it gives you a different approach?