![]() SELECT dbo.udfToLocalTime(' 8:00', DEFAULT) SELECT dbo.udfToLocalTime(' 10:00', DEFAULT) ![]() SELECT dbo.udfToLocalTime(' 9:00', DEFAULT) Function assumes that the 1st day of the week is Sunday. Function doesn't apply for years prior to 2007. Good only for US States observing the Energy Policy Act of 2005. Notes: Works on SQL Server 2008R2 and later, maybe SQL Server 2008 as well. Purpose: To convert UTC to local US time accounting for DST ![]() Here's a version that accounts for daylight savings, UTC offset, and is not locked into a particular year. Note you can not just return AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'" as the result because this result is actually a UTC time in EST format (when you compare this "fake" EST time or include it in an order clause it will be converted back to a UTC time). Select dbo.fnConvertUTCTimetoESTTime (' 4:00:00.000') Select dbo.fnConvertUTCTimetoESTTime (' 5:00:00.000') Return convert(datetime, convert(varchar(23), AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time', 121), 121) You can change your designed time zone name in this function, or get it from registry: Create Function as datetime Return function will convert a UTC time to EST time with DST adjustment. ![]() RETURNS datetime, - Second Sunday in datetime - First Sunday in November If you need a conversion other than your server's location, here is a function that allows you to pass a standard offset and accounts for US Daylight Savings Times: - = ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
February 2023
Categories |