May 14, 2020 at 2:38 pm
Please help me with the timeoffset logic calculation.
I get time offset and start time of a server as input. My requirement is to show the result to the user after 1 hr and 30 mins of start time. I'm unable to do the timeoffset logic calculation based on this data. My DB runs at -4:00 > EST Zone
ServerName 1 - TimeOffset (-07:00) - Start time - 02:00 - SAC server
ServerName 2 - TimeOffset (-04:00) Starttime - 23:45
If I use the below logic, It didn't consider the daylight issue. Please help me on achieving this
SELECT
SYSDATETIMEOFFSET() AS 'Current Date NOW',
convert(smalldatetime,SWITCHOFFSET( SYSDATETIMEOFFSET(), '+08:00' )) AS '+08:00',
convert(smalldatetime,SWITCHOFFSET( SYSDATETIMEOFFSET(), '-07:00' )) AS '-07:00 SAC',
convert(smalldatetime,SWITCHOFFSET( SYSDATETIMEOFFSET(), '+05:30' )) AS '+05:30 IST',
convert(smalldatetime,SWITCHOFFSET( SYSDATETIMEOFFSET(), '-04:00' )) AS '-04:00 EST';
May 14, 2020 at 8:48 pm
I think if you are stuck on SQL Server 2012, you are going to need to use a CASE statement to handle daylight savings time.
If you can upgrade to 2016, you can use AT TIME ZONE to have it automatically adjust based on the time zone you pick.
The way I'd do it is create 2 variables; one for daylight start and one for daylight end. If the current date is between these 2 values, then add 1 to the offset.
MIGHT want to also have an "override" variable as some places don't use daylight savings time (like where I live).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 15, 2020 at 5:16 am
I have 2012 version. How do I accomplish the offset value based on day light savings.
May 15, 2020 at 2:31 pm
CASE statement. I'm not entirely sure where you are running the query, but basically do a check if SYSDATETIMEOFFSET() > Daylight savings start date AND if SYSDATETIMEOFFSET() < Daylight savings end date. If so, then bump your SWHITCOFFSET calls up by 1 hour, otherwise leave them as they are.
Tim Cullen has a good post from 2007 to explain how to get the start and end dates and how to deal with this here:
https://www.mssqltips.com/sqlservertip/1372/daylight-savings-time-functions-in-sql-server/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply