Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Time Zone

Author  Topic 

sanjay5219
Posting Yak Master

240 Posts

Posted - 2014-08-14 : 08:52:26
Hi All,

My SQl Server is running under IST and I wanted to save the data as EST.

Please suggest and let me know if there is any technique if day light saving also consider this

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-08-14 : 11:08:29
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_States

The 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 USEasternTime
FROM
#YourTable y
INNER JOIN #USADaylightDates u ON
YEAR(dt) = YEAR(StartDate);
Go to Top of Page
   

- Advertisement -