Datediff - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Datediff

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-10-28 : 01:28:44

Dear All,
I have value in est and wanted to convert in IST. Please help
Col1
2010-10-27 23:43:36.097
2010-10-27 23:43:55.050
2010-10-27 23:48:05.160
2010-10-27 23:43:15.250
2010-10-27 23:43:17.563
2010-10-27 23:44:38.157
2010-10-27 23:44:45.957
2010-10-27 23:44:48.393

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 01:57:27

Have a look at solution given by peso.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97242

You can customize his solution as per your requirement.

TechNess
Starting Member

11 Posts

Posted - 2010-10-28 : 02:22:15

Your given time zone is EST and you want to convert to IST.
IST = GMT + 5 hours 30 minutes
EST = GMT - 4 hours
So using DATEADD function and adding 9 hours 30 minutes you can convert EST to IST as below:

Declare @myDate DATETIME
SET @myDate = '2010-10-27 23:43:36.097'
SELECT DATEADD(Minute, 30,(DATEADD(Hour, 9, @myDate))) AS [IST-TimeZone]

quote:
Originally posted by sanjay5219

Dear All,
I have value in est and wanted to convert in IST. Please help
Col1
2010-10-27 23:43:36.097
2010-10-27 23:43:55.050
2010-10-27 23:48:05.160
2010-10-27 23:43:15.250
2010-10-27 23:43:17.563
2010-10-27 23:44:38.157
2010-10-27 23:44:45.957
2010-10-27 23:44:48.393

**TechNess**

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 03:12:36

quote:
Originally posted by TechNess

Your given time zone is EST and you want to convert to IST.
IST = GMT + 5 hours 30 minutes
EST = GMT - 4 hours
So using DATEADD function and adding 9 hours 30 minutes you can convert EST to IST as below:

Declare @myDate DATETIME
SET @myDate = '2010-10-27 23:43:36.097'
SELECT DATEADD(Minute, 30,(DATEADD(Hour, 9, @myDate))) AS [IST-TimeZone]

quote:
Originally posted by sanjay5219

Dear All,
I have value in est and wanted to convert in IST. Please help
Col1
2010-10-27 23:43:36.097
2010-10-27 23:43:55.050
2010-10-27 23:48:05.160
2010-10-27 23:43:15.250
2010-10-27 23:43:17.563
2010-10-27 23:44:38.157
2010-10-27 23:44:45.957
2010-10-27 23:44:48.393

**TechNess**

How abt day light saving time change ?

TechNess
Starting Member

11 Posts

Posted - 2010-10-28 : 10:10:59

Oh I have never thought about that! In that case the way I tried will not work at some point. Can anyone tell me how to handle daylight saving during any timezone conversion?

quote:
Originally posted by pk_bohra

[quote]Originally posted by TechNess

How abt day light saving time change ?

**TechNess**

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 12:39:57

quote:
Originally posted by TechNess

Oh I have never thought about that! In that case the way I tried will not work at some point. Can anyone tell me how to handle daylight saving during any timezone conversion?

quote:
Originally posted by pk_bohra

[quote]Originally posted by TechNess

How abt day light saving time change ?

**TechNess**

Thats the reason i posted solution given by peso.
The solution is for EST & UTC but can be modified according to your requirement.

Have a look at solution given by peso.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97242

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources