Prev: SQL Server Backup overwrite?
Next: How to add a system data source for sql server 2005 express in ODBC
From: Shell on 15 Jul 2010 19:19
I was googling for an SQL TimeZone Conversion utility/function for our situation & found this article with source code:

SQL 2005 Time Zone Conversion Functions - CodeProject

http://www.codeproject.com/KB/database/SQL2005_TIME_ZONES.aspx



John Bell wrote:

HiAs you said you either need to store the offset of keep track of when it
22-Feb-08

Hi

As you said you either need to store the offset of keep track of when it
changes for historical dates. I do not think there is an easy way around
this!

John

Previous Posts In This Thread:

On Monday, January 28, 2008 7:49 AM
msnews.microsoft.com wrote:

Time Zone Conversion
Please forgive me if this is not the right group.

I have datetime values that are stored as GMT and I need to convert them to
local time.

I tried using DATEDIFF with DATEADD and that works with local time but due
to DST this will not always give the accurate time (off by 1 hour in some
cases).

Thanks,

Rob

On Monday, January 28, 2008 8:32 AM
Dan Guzman wrote:

There is nothing built into the current SQL Server version to do this.
There is nothing built into the current SQL Server version to do this.
Unless you also store the local time offset, you will need an auxiliary
table or function to facilitate the conversion. SQL 2008 will provide some
relief, though.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"msnews.microsoft.com" <robh71(a)nospam.nospam> wrote in message
news:%23JhhHxaYIHA.2268(a)TK2MSFTNGP02.phx.gbl...

On Tuesday, January 29, 2008 3:08 AM
jbellnewspost wrote:

Hi RobHave you looked at the difference between GetUTCDate and GetDate?
Hi Rob

Have you looked at the difference between GetUTCDate and GetDate?

John

"msnews.microsoft.com" wrote:

On Wednesday, January 30, 2008 2:09 AM
changli wrote:

Hi Rob,Regarding your issue, this is a known product limitation in current SQL
Hi Rob,
Regarding your issue, this is a known product limitation in current SQL
Server 2005. You may refer to this feedback:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID
=308563

In SQL Server 2008, you can use DateTimeOffset to get your local datetime
value, however I am not sure if have the same concerns as the feedback
submitter. Anyway currently as a workaround I recommend that you write a
CLR stored procedure/user defined function to implement it.

For how to CLR stored procedures/user defined functions, you may refer to
the following articles:
Creating CLR Stored Procedures
http://msdn2.microsoft.com/en-us/library/ms190790.aspx
Creating CLR Functions
http://msdn2.microsoft.com/en-us/library/ms189876.aspx

Please feel free to let me know if you have any other questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

On Tuesday, February 05, 2008 12:12 AM
--CELKO-- wrote:

Build an auxiliary table that looks like the Schema Information tablesused in
Build an auxiliary table that looks like the Schema Information tables
used in Standard SQL, then use VIEWs to display local lawful time.
Some INSTEAD OF triggers will handle UPDATE and INSERT INTO
problems.

On Thursday, February 21, 2008 11:12 AM
msnews.microsoft.com wrote:

John,I have done the math on them, but the proble is DST with relation to
John,

I have done the math on them, but the proble is DST with relation to older
dates. Certain times I am 2 hours ahead of MST and others I am 1.

The project I have taken over stores times in the database as UTC, I wonder
if I should store the given offset (for that particular moment, since it
CHANGES or just use local time. I have always used local time and never ran
into issues.

Thanks,

Rob
"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message
news:5F90CEA3-D8C8-4F23-AE34-1537980654E0(a)microsoft.com...

On Friday, February 22, 2008 5:00 PM
John Bell wrote:

HiAs you said you either need to store the offset of keep track of when it
Hi

As you said you either need to store the offset of keep track of when it
changes for historical dates. I do not think there is an easy way around
this!

John


Submitted via EggHeadCafe - Software Developer Portal of Choice
A MongoDb Cache Utility
http://www.eggheadcafe.com/tutorials/aspnet/93206c89-09c9-40fc-9296-7d74bb7996ad/a-mongodb-cache-utility.aspx
 | 
Pages: 1
Prev: SQL Server Backup overwrite?
Next: How to add a system data source for sql server 2005 express in ODBC