DateTime in UTC

[PromisedOyster]

I have a DateTime column in a database table.

How can I get the equivalent UTC value for this column?

eg something like the DateTime.ToUniversalTime() in C#.

or select DATE_COL1, getutcdate(DATE_COL1) FROM TABLE
nb: The GETUTCDATE() function returns the current utc DATE

[Simon Hayes]

It depends what you want to do exactly - is the offset from UTC based
on where the server is physically, where the clients are physically, or
something else? I don't believe there's any easy way to do this in
MSSQL, because you need to know the server's location and current UTC
offset, so you would probably need an external program which gets this
information from the operating system. If you want to base the offset
on the clients' location, then things would be more complicated,
especially if you have clients in different time zones.

If the offset is constant, you could put it in a lookup table and
create your own scalar function to modify the date, but then you would
need to handle daylight savings and so on yourself as well. So if the
C# function you mentioned already does what you want, it might be
easiest just to use it in an external program (in SQL 2005 you could
write a C# stored procedure or function to do this).

Simon

[Erland Sommarskog]

PromisedOyster (Pr************@hotmail.com) writes:

I have a DateTime column in a database table.

How can I get the equivalent UTC value for this column?

eg something like the DateTime.ToUniversalTime() in C#.

or select DATE_COL1, getutcdate(DATE_COL1) FROM TABLE
nb: The GETUTCDATE() function returns the current utc DATE

Use the dateadd() function. You will have to handle the logic for
the offset to UTC yourself, as SQL Server does not have any time zone
information.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Chandra]

hi

hope this trick would work:
select DATE_COL1, dateadd("mi", datediff("mi",GETUTCDATE() ,getdate())
,DATE_COL1)

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[Stu]

We do this all the time:

--test method
DECLARE @Date smalldatetime
SET @Date = GETDATE()

SELECT @date, DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()),
@Date), GETUTCDATE()

You could deal with minutes, but since UTC time is a change in hours,
simply your life :)

Stu

[Chandra]

Hi Stu
I think UTC time deals with 1/2 Hrs also. and because of this minute
should be correct.

For eg, India is +5.30 Hrs GMT

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[PromisedOyster]

Thanks Stu

I worked this out myself and the proc I developed is about the same as
yours. I had to use minutes though to handle Central Australian Time.

Stu wrote:

We do this all the time:

--test method
DECLARE @Date smalldatetime
SET @Date = GETDATE()

SELECT @date, DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()),
@Date), GETUTCDATE()

You could deal with minutes, but since UTC time is a change in hours,
simply your life :)

Stu

[Stu]

Really? I never knew that. I always thought that the timezones were
shifts in hours; didnot realize they shifted in half hors as well.
That's gotta be a pain for mking long distance calls.

:)

[PromisedOyster]

Stu wrote:

Really? I never knew that. I always thought that the timezones were
shifts in hours; didnot realize they shifted in half hors as well.
That's gotta be a pain for mking long distance calls.

:)

Yes, its true.

And to further complicate things, Nepal is GMT+5:45. They just had to
be different from India.