Date function that calculates time difference and DST between EST and London time (SQL Server)

Question

Currently I have a function:

CREATE FUNCTION USER.sysdate () RETURNS DATETIME AS
BEGIN  
  DECLARE @Result DATETIME

  SELECT @Result = DATEADD(hour, -5, getdate())

  RETURN @Result
END  
;

But it doesn't take into account DST. And then I realized UK has daylight saving too, so I need to deal with 2 DST. In order to build a function without manually changing "-5" to "-6" or "-4" around DST, anybody has any good ideas?

Thanks!

Are you trying to convert Eastern time into UK time? You may want to take a look into `DATETIMEOFFSET` data type, which contains time zone information. Now comes the fun part: each region switches to summer time on different days, and SQL Server doesn't know that. You must have a table which defines the days they begin summer and winter time. — Code Different, Aug 13 '14 at 19:55
Actually I'm trying to convert UK time to EST. Well I'm wondering whether I can just do query (using if statement and calculation) to figure that out instead of using DATETIMEOFFSET — user3776851, Aug 13 '14 at 20:14
I doubt you can implement this correctly on `datetime`. For instance, how would you tell between the time that's half an hour *before* the switch from DST from the time that's half an hour *after* the switch? It would be the same `datetime` value. You'd certainly need to know the current offset. — Andriy M, Aug 14 '14 at 20:45
possible duplicate of [Determining daylight saving in sql server](http://stackoverflow.com/questions/16231482/determining-daylight-saving-in-sql-server) — Matt Johnson-Pint, Aug 17 '14 at 02:46
In general, this is a function best left to your application code. SQL Server has no built-in way to do this. — Matt Johnson-Pint, Aug 17 '14 at 02:47

Date function that calculates time difference and DST between EST and London time (SQL Server)

0 Answers0