Create a table with start and end dates for the daylight savings time. The dates on which the dates fall on each year is available on the web - for example here: http://en.wikipedia.org/wiki/History_of_time_in_the_United_StatesThe table would be something like this, where I am showing only data for 3 years; you should fill in data for every year for which you want to do the calculations:CREATE TABLE #USADaylightDates( StartDate DATETIME NOT NULL, EndDate DATETIME NOT NULL PRIMARY KEY CLUSTERED (StartDate, EndDate))INSERT INTO #USADaylightDates ( StartDate , EndDate )VALUES ( '2013-03-10T02:00:00.000','2013-11-03T02:00:00.000'), ( '2014-03-09T02:00:00.000','2014-11-02T02:00:00.000'), ( '2015-03-08T02:00:00.000','2015-11-01T02:00:00.000')
Once you have this table, do the conversion like in the example below:CREATE TABLE #YourTable(dt DATETIME)INSERT INTO #YourTable VALUES ( '2014-08-14T11:12:26.000' ), ( '2014-11-24T17:10:52.000' ) SELECT Dt AS ISTDate, DATEADD(mi, CASE WHEN dt >= StartDate AND dt < EndDate THEN -570 ELSE -630 END, dt) AS USEasternTimeFROM #YourTable y INNER JOIN #USADaylightDates u ON YEAR(dt) = YEAR(StartDate);
Time Zone
Posted - 2014-08-14 : 08:52:26
