Localtime To UTC

Hi

How do I convert a smalldatetime (the local time) into the UTC time in SQL 2005? I can only see a getUTCdate() function in the system functions.

Thanks
silchesterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
can't you use that function?
0
drequinoxCommented:
use this code,
-- this is not my code, got it from internet somewhere a while ago , all credit goes to original author but would be nice to receive points for it though !!! :) , IF it works !!!
      DECLARE
          @offset TINYINT,
          @dt SMALLDATETIME,
          @sdt SMALLDATETIME,
          @edt SMALLDATETIME,
          DECLARE @i TINYINT
       
      SELECT
          @offset = 5,
          @i = 1,
          @dt = '20050201 05:32'
       
      -- find first Sunday in April
       
      WHILE @i < 7
      BEGIN
          SET @sdt = RTRIM(YEAR(@dt))+'040'+RTRIM(@i)
          IF DATEPART(weekday,@sdt)=1  
          BEGIN
              SET @i = 7
          END
          SET @i = @i + 1
      END
       
      -- find last Sunday in October
       
      SET @i = 31
      WHILE @i > 24
      BEGIN
          SET @edt = RTRIM(YEAR(@dt))+'10'+RTRIM(@i)
          IF DATEPART(weekday,@edt)=1  
          BEGIN
              SET @i = 24
          END
          SET @i = @i - 1
      END
       
      -- subtract hour from offset if within DST
       
      IF (@dt>=@sdt AND @dt<@edt)
          SET @offset = @offset - 1
       
      SELECT @dt AS CurrentTime, DATEADD(hour, @offset, @dt) AS UTCTime

 
0
RiteshShahCommented:
can't you use something like, find difference between your time and UTC time and add those time difference to your own dateField?

DateAdd(minute,DATEDIFF(minute, GetUtcDate(), GetDate()), YourDatefield)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

silchesterAuthor Commented:
Sorry guys, do not think I explained this well:

I do not want to convert the current time to UTC.

I want to convert an arbitrary smalldatetime into its equivelent UTC.

So not wanting to refer to the current time at all, that is why the getUTCDate() function is not applicable.

So, want the UTC of for example: '9/22/2009 1:25:00 PM'

Hope this helps
0
RiteshShahCommented:
eventhough this will work,


select DATEADD(MINUTE,datediff(MINUTE,GETDATE(),getUTCdate()),getdate()),getutcdate()
0
RiteshShahCommented:
look at this:


declare @dt datetime
set @dt='9/22/2009 1:25:00 PM'
--select @dt
select DATEADD(MINUTE,datediff(MINUTE,GETDATE(),getUTCdate()),@dt)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
silchesterAuthor Commented:
Hi Ritesh

Ok, I have entered your code into SQL 2005 Server Express SQL Query.

I must be being thick. But the results column shows :

2009-09-22 13:25:00.000

I was expecting an integer representing the number of seconds since midnight. I am just looking to see if I have to alter the results format....

I tried:

declare @utc_result AS bigint

SET @utc_result = <select DATEADD........ >

No luck...
0
silchesterAuthor Commented:
Hi Ritesh
I reckon my question title was misleading - i am not bothered about localtime. so will close this question and ask a new one.
many thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.