You should not being storing times in either EST or IST ... use UTC dates instead.
i.e. use
GETUTCDATE()
rather than
GETDATE()
All time adjustments should be done using the Locale in your presentation layer (I can't comment further on how to do this as you haven't said how you are presenting this data)
Part of the problem is having to adjust for "Daylight Saving Time" or "Summer Time" in those timezones that use it - EST being one of them (aka EDT)
For example the blog link in Solution 2 states that EST is UTC-5 but this will change to UTC-4 if EDT is in place.
References:
EST - Eastern Standard Time (Time Zone Abbreviation)[
^]
EDT - Eastern Daylight Time (Time Zone Abbreviation)[
^]
So be aware that if you do store the dates in a specific format you still may not be displaying the correct time to the user.
To convert an existing column into UTC time you can use a combination of GETUTCDATE and GETDATE to determine the difference between the current time (on the SQL server) and UTC time.
For example, I used some sample data generated as follows:
WITH q AS
(
SELECT GETDATE() AS datum
UNION ALL
SELECT dateadd(hh, 5, datum)
FROM q
WHERE dateadd(hh, 5, datum) < dateadd(dd, 15, GETDATE())
)
SELECT datum, cast(null as datetime) as datum2
into #temp
FROM q
(Basically just a list of datetimes starting from now and adding 5 hours per row)
To set column datum2 to UTC time I can use the following:
update #temp set datum2 = DATEADD(hh, (-1) * (DATEDIFF(hh,GETUTCDATE(),GETDATE())), datum)
If you really
muststore the data in EST then just amend the line of code to subsequently do that conversion (i.e. take the times to UTC first then convert to your target timezone). E.g.
update #temp set datum2 = DATEADD(hh, - 5, DATEADD(hh, (-1) * (DATEDIFF(hh,GETUTCDATE(),GETDATE())), datum))