Prev: Top 10 Technical requirements for In-Memory Reporting
Next: reThink Migration – It’s Time for Change!
From: Iain Sharp on 28 May 2010 10:32
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

From: J M De Moor on 28 May 2010 13:15
Iain

>
> 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 am not sure I totally understand, but what about:

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

From: Erland Sommarskog on 28 May 2010 17:21
Iain Sharp (iains(a)pciltd.co.uk) writes:
> 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?

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Iain Sharp on 1 Jun 2010 05:31
On Fri, 28 May 2010 23:21:32 +0200, Erland Sommarskog
<esquel(a)sommarskog.se> wrote:

>Iain Sharp (iains(a)pciltd.co.uk) writes:
>> 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?
>
>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.

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
From: Philipp Post on 1 Jun 2010 09:25
> 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. <

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
 |  Next  |  Last
Pages: 1 2
Prev: Top 10 Technical requirements for In-Memory Reporting
Next: reThink Migration – It’s Time for Change!