I have an database with many datetime columns in many different
entities.

My application language has theoretically the facility to adjust the
times based on the variation between the client locale and the server
locale, but it's really buggy.

Is there a feature I can use in SQL server 2005 which can apply the
offset during insert update and select based on the database user
log-in? (i.e. so that the dates are stored in GMT but as far as that
database user is concerned they will be in USA(EST)?)

I'm hoping that the application language will fix the bugs, allowing
me to do this in the application, but until then, any ideas?

Iain

Iain

I am not sure I totally understand, but what about:

SELECT DATEDIFF(hh, GetUTCDate(), GETDATE());

Iain Sharp (iains (AT) pciltd (DOT) co.uk) writes:
SQL Server has no information about the client's timezone. So if the
client is in New York, and sends in 2010-05-29 13:31 and this is -04:00,
there is no way you can no this in SQL Server with less than passing
the time zone from the client.

If you have the offset, dateadd will help you. But that is also all
you have.

In SQL 2008 there are some new datetime data types, including
datetimeoffset, so that you can store a time-zone value with the
datetime value.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Fri, 28 May 2010 23:21:32 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Yeah, that's mostly what I was afraid of. I was hoping that there was
a timezone offset as an attribute of a database user or something
which would do this for me universally.

I guess I'll just have to store the dates and times as EST until the
application software bugs are ironed out, and then trawl the data
dictionary looking for datetime fields to alter.

Iain

If your users do not travel much and you know where they work you
could store the UTC offset in the user profile and use DATEDIFF to
compute the UTC. Downside is that you will have to alter the user
settings based on local summer / winter time rules.

brgds

Philipp Post

On Tue, 1 Jun 2010 06:25:03 -0700 (PDT), Philipp Post
<post.philipp (AT) googlemail (DOT) com> wrote:

Yes I certainly could. If I wasn't waiting for my application
programming language to fix a bug which prevents it from doing exactly
this for me, I probably would. However, there are hundreds of
date-time columns, and I think missing one or two would be worse than
knowing that the dates were all off by the same factor.

If there's no generic 'environment' switch or function for this, then
I have a backup plan, but thanks for your suggestion.

Iain