|
Home > Archive > Microsoft SQL Server forum > August 2005 > DateTime in UTC
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| PromisedOyster 2005-08-02, 8:24 pm |
| 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 2005-08-03, 3:23 am |
| 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 2005-08-03, 3:23 am |
| PromisedOyster (PromisedOyster@hotm
ail.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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
|
|
| 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
| |
|
|
| PromisedOyster 2005-08-04, 9:24 am |
| 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
| |
|
| 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 2005-08-05, 9:24 am |
|
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.
|
|
|
|
|