September 24, 2010 at 9:14 am
I have an unusual request from a developer, wonder if anyone else has had a similar situation and how best to deal with it.
Its a third party app so limited options on altering design. The user is writing reports from the database using Business Objects.
The application stores all dates as date/times in UTC (GMT) format. When displaying the data, the application determines whether the date/time was within the BST period and if it is, subtracts 1 hour. I'm told that this is done using browser settings but know nothing further than that.
To provide an example:
If a call is created on 01/03/2010 at 10:00, then that is exactly what is stored on the database. If however the call was created on 01/04/2010 at 10:00, then the database field is set to the UTC equivilant - 01/04/2010 09:00.
the problem is to look at this field, determine if the date/time is within BST, and if so add 1 hour. If it isn't, then to display it as stored.
Adding the hour we can do, but how to best identify if the date/time is within BST? Any ideas?
---------------------------------------------------------------------
September 24, 2010 at 12:05 pm
George I've got a TallyCalendar table and the code which updated/added a column to determine if the date in the table was DaylightSavings time or not...is that the same as BST in your question?
In my case, joining to a TallyCalendar table and using the IsDaylightSavings integer to determine whether to add an hour or not is what I usually use.
Lowell
September 24, 2010 at 1:04 pm
Lowell, Hi, thanks for replying
BST is British Summer Time, the clocks go forward one hour in the spring and back an hour in the autumn, so I would say that is the same as DST.
What you are doing sounds do-able for us. We should be able to go as far as add an extra table, I was expecting to have to make some changes in the database.
cheers
george
---------------------------------------------------------------------
September 24, 2010 at 1:10 pm
Keep in mind these "switch" weekends are determined on a country bases.
As in many cases in Europe, every country can do it differently 😉
These are the dates I found in use for Belgium, maybe it's a start for you.
/* SQL2000 */
Create function fn_ALZDBA_GMT_2_LT_Brussels ( @dtGMT datetime )
returns table
with schemabinding
as
/* bron: http://www.astro.oma.be/GENERAL/INFO/nli001.html
wintertijd = GMT + 1
zomertijd = GMT + 2
*/
Return (
/* Wat is de correctie */
Select dateadd( HH, case when @dtGMT >= '1918-03-09 00:00:00.000' AND @dtGMT < '1918-10-05 23:00:00.000' then 1
when @dtGMT >= '1918-10-05 23:00:00.000' AND @dtGMT < '1919-03-01 23:00:00.000' then 0
when @dtGMT >= '1919-03-01 23:00:00.000' AND @dtGMT < '1919-10-04 23:00:00.000' then 1
when @dtGMT >= '1919-10-04 23:00:00.000' AND @dtGMT < '1920-02-14 23:00:00.000' then 0
when @dtGMT >= '1920-02-14 23:00:00.000' AND @dtGMT < '1920-10-23 23:00:00.000' then 1
when @dtGMT >= '1920-10-23 23:00:00.000' AND @dtGMT < '1921-03-14 23:00:00.000' then 0
when @dtGMT >= '1921-03-14 23:00:00.000' AND @dtGMT < '1921-10-25 23:00:00.000' then 1
when @dtGMT >= '1921-10-25 23:00:00.000' AND @dtGMT < '1922-03-25 23:00:00.000' then 0
when @dtGMT >= '1922-03-25 23:00:00.000' AND @dtGMT < '1922-10-07 23:00:00.000' then 1
when @dtGMT >= '1922-10-07 23:00:00.000' AND @dtGMT < '1923-04-21 23:00:00.000' then 0
when @dtGMT >= '1923-04-21 23:00:00.000' AND @dtGMT < '1923-10-06 23:00:00.000' then 1
when @dtGMT >= '1923-10-06 23:00:00.000' AND @dtGMT < '1924-03-29 23:00:00.000' then 0
when @dtGMT >= '1924-03-29 23:00:00.000' AND @dtGMT < '1924-10-04 23:00:00.000' then 1
when @dtGMT >= '1924-10-04 23:00:00.000' AND @dtGMT < '1925-04-04 23:00:00.000' then 0
when @dtGMT >= '1925-04-04 23:00:00.000' AND @dtGMT < '1925-10-03 23:00:00.000' then 1
when @dtGMT >= '1925-10-03 23:00:00.000' AND @dtGMT < '1926-04-17 23:00:00.000' then 0
when @dtGMT >= '1926-04-17 23:00:00.000' AND @dtGMT < '1926-10-02 23:00:00.000' then 1
when @dtGMT >= '1926-10-02 23:00:00.000' AND @dtGMT < '1927-04-09 23:00:00.000' then 0
when @dtGMT >= '1927-04-09 23:00:00.000' AND @dtGMT < '1927-10-01 23:00:00.000' then 1
when @dtGMT >= '1927-10-01 23:00:00.000' AND @dtGMT < '1928-04-14 23:00:00.000' then 0
when @dtGMT >= '1928-04-14 23:00:00.000' AND @dtGMT < '1928-10-07 02:00:00.000' then 1
when @dtGMT >= '1928-10-07 02:00:00.000' AND @dtGMT < '1929-04-21 02:00:00.000' then 0
when @dtGMT >= '1929-04-21 02:00:00.000' AND @dtGMT < '1929-10-06 02:00:00.000' then 1
when @dtGMT >= '1929-10-06 02:00:00.000' AND @dtGMT < '1930-04-13 02:00:00.000' then 0
when @dtGMT >= '1930-04-13 02:00:00.000' AND @dtGMT < '1930-10-05 02:00:00.000' then 1
when @dtGMT >= '1930-10-05 02:00:00.000' AND @dtGMT < '1931-04-19 02:00:00.000' then 0
when @dtGMT >= '1931-04-19 02:00:00.000' AND @dtGMT < '1931-10-04 02:00:00.000' then 1
when @dtGMT >= '1931-10-04 02:00:00.000' AND @dtGMT < '1932-04-03 02:00:00.000' then 0
when @dtGMT >= '1932-04-03 02:00:00.000' AND @dtGMT < '1932-10-02 02:00:00.000' then 1
when @dtGMT >= '1932-10-02 02:00:00.000' AND @dtGMT < '1933-03-26 02:00:00.000' then 0
when @dtGMT >= '1933-03-26 02:00:00.000' AND @dtGMT < '1933-10-08 02:00:00.000' then 1
when @dtGMT >= '1933-10-08 02:00:00.000' AND @dtGMT < '1934-04-08 02:00:00.000' then 0
when @dtGMT >= '1934-04-08 02:00:00.000' AND @dtGMT < '1934-10-07 02:00:00.000' then 1
when @dtGMT >= '1934-10-07 02:00:00.000' AND @dtGMT < '1935-03-31 02:00:00.000' then 0
when @dtGMT >= '1935-03-31 02:00:00.000' AND @dtGMT < '1935-10-06 02:00:00.000' then 1
when @dtGMT >= '1935-10-06 02:00:00.000' AND @dtGMT < '1936-04-19 02:00:00.000' then 0
when @dtGMT >= '1936-04-19 02:00:00.000' AND @dtGMT < '1936-10-04 02:00:00.000' then 1
when @dtGMT >= '1936-10-04 02:00:00.000' AND @dtGMT < '1937-04-04 02:00:00.000' then 0
when @dtGMT >= '1937-04-04 02:00:00.000' AND @dtGMT < '1937-10-03 02:00:00.000' then 1
when @dtGMT >= '1937-10-03 02:00:00.000' AND @dtGMT < '1938-03-27 02:00:00.000' then 0
when @dtGMT >= '1938-03-27 02:00:00.000' AND @dtGMT < '1938-10-02 02:00:00.000' then 1
when @dtGMT >= '1938-10-02 02:00:00.000' AND @dtGMT < '1939-04-16 02:00:00.000' then 0
when @dtGMT >= '1939-04-16 02:00:00.000' AND @dtGMT < '1939-11-19 02:00:00.000' then 1
when @dtGMT >= '1939-11-19 02:00:00.000' AND @dtGMT < '1940-02-25 02:00:00.000' then 0
when @dtGMT >= '1940-02-25 02:00:00.000' AND @dtGMT < '1940-05-20 02:00:00.000' then 1
when @dtGMT >= '1940-05-20 02:00:00.000' AND @dtGMT < '1942-11-02 01:00:00.000' then 2
when @dtGMT >= '1942-11-02 01:00:00.000' AND @dtGMT < '1943-03-29 01:00:00.000' then 1
when @dtGMT >= '1943-03-29 01:00:00.000' AND @dtGMT < '1943-10-04 01:00:00.000' then 2
when @dtGMT >= '1943-10-04 01:00:00.000' AND @dtGMT < '1944-04-03 01:00:00.000' then 1
when @dtGMT >= '1944-04-03 01:00:00.000' AND @dtGMT < '1944-09-17 01:00:00.000' then 2
when @dtGMT >= '1944-09-17 01:00:00.000' AND @dtGMT < '1945-04-02 01:00:00.000' then 1
when @dtGMT >= '1945-04-02 01:00:00.000' AND @dtGMT < '1945-09-16 01:00:00.000' then 2
when @dtGMT >= '1945-09-16 01:00:00.000' AND @dtGMT < '1946-05-19 01:00:00.000' then 1
when @dtGMT >= '1946-05-19 01:00:00.000' AND @dtGMT < '1946-10-07 01:00:00.000' then 2
when @dtGMT >= '1946-10-07 01:00:00.000' AND @dtGMT < '1977-04-03 01:00:00.000' then 1
when @dtGMT >= '1977-04-03 01:00:00.000' AND @dtGMT < '1977-09-25 01:00:00.000' then 2
when @dtGMT >= '1977-09-25 01:00:00.000' AND @dtGMT < '1978-04-02 01:00:00.000' then 1
when @dtGMT >= '1978-04-02 01:00:00.000' AND @dtGMT < '1978-10-01 01:00:00.000' then 2
when @dtGMT >= '1978-10-01 01:00:00.000' AND @dtGMT < '1979-04-01 01:00:00.000' then 1
when @dtGMT >= '1979-04-01 01:00:00.000' AND @dtGMT < '1979-09-30 01:00:00.000' then 2
when @dtGMT >= '1979-09-30 01:00:00.000' AND @dtGMT < '1980-04-06 01:00:00.000' then 1
when @dtGMT >= '1980-04-06 01:00:00.000' AND @dtGMT < '1980-09-28 01:00:00.000' then 2
when @dtGMT >= '1980-09-28 01:00:00.000' AND @dtGMT < '1981-03-29 01:00:00.000' then 1
when @dtGMT >= '1981-03-29 01:00:00.000' AND @dtGMT < '1981-09-27 01:00:00.000' then 2
when @dtGMT >= '1981-09-27 01:00:00.000' AND @dtGMT < '1982-03-28 01:00:00.000' then 1
when @dtGMT >= '1982-03-28 01:00:00.000' AND @dtGMT < '1982-09-26 01:00:00.000' then 2
when @dtGMT >= '1982-09-26 01:00:00.000' AND @dtGMT < '1983-03-27 01:00:00.000' then 1
when @dtGMT >= '1983-03-27 01:00:00.000' AND @dtGMT < '1983-09-25 01:00:00.000' then 2
when @dtGMT >= '1983-09-25 01:00:00.000' AND @dtGMT < '1984-03-25 01:00:00.000' then 1
when @dtGMT >= '1984-03-25 01:00:00.000' AND @dtGMT < '1984-09-30 01:00:00.000' then 2
when @dtGMT >= '1984-09-30 01:00:00.000' AND @dtGMT < '1985-03-31 01:00:00.000' then 1
when @dtGMT >= '1985-03-31 01:00:00.000' AND @dtGMT < '1985-09-29 01:00:00.000' then 2
when @dtGMT >= '1985-09-29 01:00:00.000' AND @dtGMT < '1986-03-30 01:00:00.000' then 1
when @dtGMT >= '1986-03-30 01:00:00.000' AND @dtGMT < '1986-09-28 01:00:00.000' then 2
when @dtGMT >= '1986-09-28 01:00:00.000' AND @dtGMT < '1987-03-29 01:00:00.000' then 1
when @dtGMT >= '1987-03-29 01:00:00.000' AND @dtGMT < '1987-09-27 01:00:00.000' then 2
when @dtGMT >= '1987-09-27 01:00:00.000' AND @dtGMT < '1988-03-27 01:00:00.000' then 1
when @dtGMT >= '1988-03-27 01:00:00.000' AND @dtGMT < '1988-09-25 01:00:00.000' then 2
when @dtGMT >= '1988-09-25 01:00:00.000' AND @dtGMT < '1989-03-26 01:00:00.000' then 1
when @dtGMT >= '1989-03-26 01:00:00.000' AND @dtGMT < '1989-09-24 01:00:00.000' then 2
when @dtGMT >= '1989-09-24 01:00:00.000' AND @dtGMT < '1990-03-25 01:00:00.000' then 1
when @dtGMT >= '1990-03-25 01:00:00.000' AND @dtGMT < '1990-09-30 01:00:00.000' then 2
when @dtGMT >= '1990-09-30 01:00:00.000' AND @dtGMT < '1991-03-31 01:00:00.000' then 1
when @dtGMT >= '1991-03-31 01:00:00.000' AND @dtGMT < '1991-09-29 01:00:00.000' then 2
when @dtGMT >= '1991-09-29 01:00:00.000' AND @dtGMT < '1992-03-29 01:00:00.000' then 1
when @dtGMT >= '1992-03-29 01:00:00.000' AND @dtGMT < '1992-09-27 01:00:00.000' then 2
when @dtGMT >= '1992-09-27 01:00:00.000' AND @dtGMT < '1993-03-28 01:00:00.000' then 1
when @dtGMT >= '1993-03-28 01:00:00.000' AND @dtGMT < '1993-09-26 01:00:00.000' then 2
when @dtGMT >= '1993-09-26 01:00:00.000' AND @dtGMT < '1994-03-27 01:00:00.000' then 1
when @dtGMT >= '1994-03-27 01:00:00.000' AND @dtGMT < '1994-09-25 01:00:00.000' then 2
when @dtGMT >= '1994-09-25 01:00:00.000' AND @dtGMT < '1995-03-26 01:00:00.000' then 1
when @dtGMT >= '1995-03-26 01:00:00.000' AND @dtGMT < '1995-09-24 01:00:00.000' then 2
when @dtGMT >= '1995-09-24 01:00:00.000' AND @dtGMT < '1996-03-31 01:00:00.000' then 1
when @dtGMT >= '1996-03-31 01:00:00.000' AND @dtGMT < '1996-10-27 01:00:00.000' then 2
when @dtGMT >= '1996-10-27 01:00:00.000' AND @dtGMT < '1997-03-30 01:00:00.000' then 1
when @dtGMT >= '1997-03-30 01:00:00.000' AND @dtGMT < '1997-10-26 01:00:00.000' then 2
when @dtGMT >= '1997-10-26 01:00:00.000' AND @dtGMT < '1998-03-29 01:00:00.000' then 1
when @dtGMT >= '1998-03-29 01:00:00.000' AND @dtGMT < '1998-10-25 01:00:00.000' then 2
when @dtGMT >= '1998-10-25 01:00:00.000' AND @dtGMT < '1999-03-28 01:00:00.000' then 1
when @dtGMT >= '1999-03-28 01:00:00.000' AND @dtGMT < '1999-10-31 01:00:00.000' then 2
when @dtGMT >= '1999-10-31 01:00:00.000' AND @dtGMT < '2000-03-26 01:00:00.000' then 1
when @dtGMT >= '2000-03-26 01:00:00.000' AND @dtGMT < '2000-10-29 01:00:00.000' then 2
when @dtGMT >= '2000-10-29 01:00:00.000' AND @dtGMT < '2001-03-25 01:00:00.000' then 1
when @dtGMT >= '2001-03-25 01:00:00.000' AND @dtGMT < '2001-10-28 01:00:00.000' then 2
when @dtGMT >= '2001-10-28 01:00:00.000' AND @dtGMT < '2002-03-31 01:00:00.000' then 1
when @dtGMT >= '2002-03-31 01:00:00.000' AND @dtGMT < '2002-10-27 01:00:00.000' then 2
when @dtGMT >= '2002-10-27 01:00:00.000' AND @dtGMT < '2003-03-30 01:00:00.000' then 1
when @dtGMT >= '2003-03-30 01:00:00.000' AND @dtGMT < '2003-10-26 01:00:00.000' then 2
when @dtGMT >= '2003-10-26 01:00:00.000' AND @dtGMT < '2004-03-28 01:00:00.000' then 1
when @dtGMT >= '2004-03-28 01:00:00.000' AND @dtGMT < '2004-10-31 01:00:00.000' then 2
when @dtGMT >= '2004-10-31 01:00:00.000' AND @dtGMT < '2005-03-27 01:00:00.000' then 1
when @dtGMT >= '2005-03-27 01:00:00.000' AND @dtGMT < '2005-10-30 01:00:00.000' then 2
when @dtGMT >= '2005-10-30 01:00:00.000' AND @dtGMT < '2006-03-26 01:00:00.000' then 1
when @dtGMT >= '2006-03-26 01:00:00.000' AND @dtGMT < '2006-10-29 01:00:00.000' then 2
when @dtGMT >= '2006-10-29 01:00:00.000' AND @dtGMT < '2007-03-25 01:00:00.000' then 1
when @dtGMT >= '2007-03-25 01:00:00.000' AND @dtGMT < '2007-10-28 01:00:00.000' then 2
when @dtGMT >= '2007-10-28 01:00:00.000' AND @dtGMT < '2008-03-30 01:00:00.000' then 1
when @dtGMT >= '2008-03-30 01:00:00.000' AND @dtGMT < '2008-10-26 01:00:00.000' then 2
when @dtGMT >= '2008-10-26 01:00:00.000' AND @dtGMT < '2009-03-29 01:00:00.000' then 1
when @dtGMT >= '2009-03-29 01:00:00.000' AND @dtGMT < '2009-10-25 01:00:00.000' then 2
when @dtGMT >= '2009-10-25 01:00:00.000' AND @dtGMT < '2010-03-28 01:00:00.000' then 1
when @dtGMT >= '2010-03-28 01:00:00.000' AND @dtGMT < '2010-10-31 01:00:00.000' then 2
when @dtGMT >= '2010-10-31 01:00:00.000' AND @dtGMT < '2011-03-27 01:00:00.000' then 1
when @dtGMT >= '2011-03-27 01:00:00.000' AND @dtGMT < '2011-10-30 01:00:00.000' then 2
when @dtGMT >= '2011-10-30 01:00:00.000' AND @dtGMT < '2012-03-26 01:00:00.000' then 1
when @dtGMT >= '2012-03-26 01:00:00.000' AND @dtGMT < '2012-10-28 01:00:00.000' then 2
when @dtGMT >= '2012-10-28 01:00:00.000' AND @dtGMT < '2013-03-31 01:00:00.000' then 1
when @dtGMT >= '2013-03-31 01:00:00.000' AND @dtGMT < '2013-10-27 01:00:00.000' then 2
when @dtGMT >= '2013-10-27 01:00:00.000' AND @dtGMT < '2014-03-30 01:00:00.000' then 1
when @dtGMT >= '2014-03-30 01:00:00.000' AND @dtGMT < '2014-10-26 01:00:00.000' then 2
when @dtGMT >= '2014-10-26 01:00:00.000' AND @dtGMT < '2015-03-29 01:00:00.000' then 1
when @dtGMT >= '2015-03-29 01:00:00.000' AND @dtGMT < '2015-10-26 01:00:00.000' then 2
when @dtGMT >= '2015-10-26 01:00:00.000' AND @dtGMT < '2016-03-27 01:00:00.000' then 1
when @dtGMT >= '2016-03-27 01:00:00.000' AND @dtGMT < '2016-10-30 01:00:00.000' then 2
when @dtGMT >= '2016-10-30 01:00:00.000' AND @dtGMT < '2017-03-26 01:00:00.000' then 1
when @dtGMT >= '2017-03-26 01:00:00.000' AND @dtGMT < '2017-10-29 01:00:00.000' then 2
when @dtGMT >= '2017-10-29 01:00:00.000' AND @dtGMT < '2018-03-26 01:00:00.000' then 1
when @dtGMT >= '2018-03-26 01:00:00.000' AND @dtGMT < '2018-10-28 01:00:00.000' then 2
when @dtGMT >= '2018-10-28 01:00:00.000' AND @dtGMT < '2019-03-31 01:00:00.000' then 1
when @dtGMT >= '2019-03-31 01:00:00.000' AND @dtGMT < '2019-10-27 01:00:00.000' then 2
when @dtGMT >= '2019-10-27 01:00:00.000' AND @dtGMT < '2020-03-29 01:00:00.000' then 1
when @dtGMT >= '2020-03-29 01:00:00.000' AND @dtGMT < '2020-10-26 01:00:00.000' then 2
when @dtGMT >= '2020-10-26 01:00:00.000' AND @dtGMT < '2021-03-28 01:00:00.000' then 1
when @dtGMT >= '2021-03-28 01:00:00.000' AND @dtGMT < '2021-10-31 01:00:00.000' then 2
when @dtGMT >= '2021-10-31 01:00:00.000' AND @dtGMT < '2022-03-27 01:00:00.000' then 1
when @dtGMT >= '2022-03-27 01:00:00.000' AND @dtGMT < '2022-10-30 01:00:00.000' then 2
when @dtGMT >= '2022-10-30 01:00:00.000' AND @dtGMT < '2023-03-26 01:00:00.000' then 1
when @dtGMT >= '2023-03-26 01:00:00.000' AND @dtGMT < '2023-10-29 01:00:00.000' then 2
when @dtGMT >= '2023-10-29 01:00:00.000' AND @dtGMT < '2024-03-31 01:00:00.000' then 1
when @dtGMT >= '2024-03-31 01:00:00.000' AND @dtGMT < '2024-10-27 01:00:00.000' then 2
when @dtGMT >= '2024-10-27 01:00:00.000' AND @dtGMT < '2025-03-30 01:00:00.000' then 1
when @dtGMT >= '2025-03-30 01:00:00.000' AND @dtGMT < '2025-10-26 01:00:00.000' then 2
when @dtGMT >= '2025-10-26 01:00:00.000' AND @dtGMT < '2026-03-29 01:00:00.000' then 1
when @dtGMT >= '2026-03-29 01:00:00.000' AND @dtGMT < '2026-10-26 01:00:00.000' then 2
when @dtGMT >= '2026-10-26 01:00:00.000' AND @dtGMT < '2027-03-28 01:00:00.000' then 1
when @dtGMT >= '2027-03-28 01:00:00.000' AND @dtGMT < '2027-10-31 01:00:00.000' then 2
when @dtGMT >= '2027-10-31 01:00:00.000' AND @dtGMT < '2028-03-26 01:00:00.000' then 1
when @dtGMT >= '2028-03-26 01:00:00.000' AND @dtGMT < '2028-10-29 01:00:00.000' then 2
when @dtGMT >= '2028-10-29 01:00:00.000' AND @dtGMT < '2029-03-26 01:00:00.000' then 1
when @dtGMT >= '2029-03-26 01:00:00.000' AND @dtGMT < '2029-10-28 01:00:00.000' then 2
when @dtGMT >= '2029-10-28 01:00:00.000' AND @dtGMT < '2030-03-31 01:00:00.000' then 1
when @dtGMT >= '2030-03-31 01:00:00.000' AND @dtGMT < '2030-10-27 01:00:00.000' then 2
when @dtGMT >= '2030-10-27 01:00:00.000' AND @dtGMT < '2031-03-30 01:00:00.000' then 1
when @dtGMT >= '2031-03-30 01:00:00.000' AND @dtGMT < '2031-10-26 01:00:00.000' then 2
when @dtGMT >= '2031-10-26 01:00:00.000' AND @dtGMT < '2032-03-28 01:00:00.000' then 1
when @dtGMT >= '2032-03-28 01:00:00.000' AND @dtGMT < '2032-10-31 01:00:00.000' then 2
when @dtGMT >= '2032-10-31 01:00:00.000' AND @dtGMT < '2033-03-27 01:00:00.000' then 1
when @dtGMT >= '2033-03-27 01:00:00.000' AND @dtGMT < '2033-10-30 01:00:00.000' then 2
when @dtGMT >= '2033-10-30 01:00:00.000' AND @dtGMT < '2034-03-26 01:00:00.000' then 1
when @dtGMT >= '2034-03-26 01:00:00.000' AND @dtGMT < '2034-10-29 01:00:00.000' then 2
when @dtGMT >= '2034-10-29 01:00:00.000' AND @dtGMT < '2035-03-26 01:00:00.000' then 1
when @dtGMT >= '2035-03-26 01:00:00.000' AND @dtGMT < '2035-10-28 01:00:00.000' then 2
when @dtGMT >= '2035-10-28 01:00:00.000' AND @dtGMT < '2036-03-30 01:00:00.000' then 1
when @dtGMT >= '2036-03-30 01:00:00.000' AND @dtGMT < '2036-10-26 01:00:00.000' then 2
when @dtGMT >= '2036-10-26 01:00:00.000' AND @dtGMT < '2037-03-29 01:00:00.000' then 1
when @dtGMT >= '2037-03-29 01:00:00.000' AND @dtGMT < '2037-10-26 01:00:00.000' then 2
when @dtGMT >= '2037-10-26 01:00:00.000' AND @dtGMT < '2038-03-28 01:00:00.000' then 1
when @dtGMT >= '2038-03-28 01:00:00.000' AND @dtGMT < '2038-10-31 01:00:00.000' then 2
when @dtGMT >= '2038-10-31 01:00:00.000' AND @dtGMT < '2039-03-27 01:00:00.000' then 1
when @dtGMT >= '2039-03-27 01:00:00.000' AND @dtGMT < '2039-10-30 01:00:00.000' then 2
when @dtGMT >= '2039-10-30 01:00:00.000' AND @dtGMT < '2040-03-26 01:00:00.000' then 1
when @dtGMT >= '2040-03-26 01:00:00.000' AND @dtGMT < '2040-10-28 01:00:00.000' then 2
when @dtGMT >= '2040-10-28 01:00:00.000' AND @dtGMT < '2041-03-31 01:00:00.000' then 1
when @dtGMT >= '2041-03-31 01:00:00.000' AND @dtGMT < '2041-10-27 01:00:00.000' then 2
when @dtGMT >= '2041-10-27 01:00:00.000' AND @dtGMT < '2042-03-30 01:00:00.000' then 1
when @dtGMT >= '2042-03-30 01:00:00.000' AND @dtGMT < '2042-10-26 01:00:00.000' then 2
when @dtGMT >= '2042-10-26 01:00:00.000' AND @dtGMT < '2043-03-29 01:00:00.000' then 1
when @dtGMT >= '2043-03-29 01:00:00.000' AND @dtGMT < '2043-10-26 01:00:00.000' then 2
when @dtGMT >= '2043-10-26 01:00:00.000' AND @dtGMT < '2044-03-27 01:00:00.000' then 1
when @dtGMT >= '2044-03-27 01:00:00.000' AND @dtGMT < '2044-10-30 01:00:00.000' then 2
when @dtGMT >= '2044-10-30 01:00:00.000' AND @dtGMT < '2045-03-26 01:00:00.000' then 1
when @dtGMT >= '2045-03-26 01:00:00.000' AND @dtGMT < '2045-10-29 01:00:00.000' then 2
when @dtGMT >= '2045-10-29 01:00:00.000' AND @dtGMT < '2046-03-26 01:00:00.000' then 1
when @dtGMT >= '2046-03-26 01:00:00.000' AND @dtGMT < '2046-10-28 01:00:00.000' then 2
when @dtGMT >= '2046-10-28 01:00:00.000' AND @dtGMT < '2047-03-31 01:00:00.000' then 1
when @dtGMT >= '2047-03-31 01:00:00.000' AND @dtGMT < '2047-10-27 01:00:00.000' then 2
when @dtGMT >= '2047-10-27 01:00:00.000' AND @dtGMT < '2048-03-29 01:00:00.000' then 1
when @dtGMT >= '2048-03-29 01:00:00.000' AND @dtGMT < '2048-10-26 01:00:00.000' then 2
when @dtGMT >= '2048-10-26 01:00:00.000' AND @dtGMT < '2049-03-28 01:00:00.000' then 1
when @dtGMT >= '2049-03-28 01:00:00.000' AND @dtGMT < '2049-10-31 01:00:00.000' then 2
when @dtGMT >= '2049-10-31 01:00:00.000' AND @dtGMT < '2050-03-27 01:00:00.000' then 1
when @dtGMT >= '2050-03-27 01:00:00.000' AND @dtGMT < '2050-10-30 01:00:00.000' then 2
when @dtGMT >= '2050-10-30 01:00:00.000' AND @dtGMT < '2051-03-26 01:00:00.000' then 1
when @dtGMT >= '2051-03-26 01:00:00.000' AND @dtGMT < '2051-10-29 01:00:00.000' then 2
when @dtGMT >= '2051-10-29 01:00:00.000' AND @dtGMT < '2052-03-31 01:00:00.000' then 1
when @dtGMT >= '2052-03-31 01:00:00.000' AND @dtGMT < '2052-10-27 01:00:00.000' then 2
when @dtGMT >= '2052-10-27 01:00:00.000' AND @dtGMT < '2053-03-30 01:00:00.000' then 1
when @dtGMT >= '2053-03-30 01:00:00.000' AND @dtGMT < '2053-10-26 01:00:00.000' then 2
when @dtGMT >= '2053-10-26 01:00:00.000' AND @dtGMT < '2054-03-29 01:00:00.000' then 1
when @dtGMT >= '2054-03-29 01:00:00.000' AND @dtGMT < '2054-10-26 01:00:00.000' then 2
when @dtGMT >= '2054-10-26 01:00:00.000' AND @dtGMT < '2055-03-28 01:00:00.000' then 1
when @dtGMT >= '2055-03-28 01:00:00.000' AND @dtGMT < '2055-10-31 01:00:00.000' then 2
when @dtGMT >= '2055-10-31 01:00:00.000' AND @dtGMT < '2056-03-26 01:00:00.000' then 1
when @dtGMT >= '2056-03-26 01:00:00.000' AND @dtGMT < '2056-10-29 01:00:00.000' then 2
when @dtGMT >= '2056-10-29 01:00:00.000' AND @dtGMT < '2057-03-26 01:00:00.000' then 1
when @dtGMT >= '2057-03-26 01:00:00.000' AND @dtGMT < '2057-10-28 01:00:00.000' then 2
when @dtGMT >= '2057-10-28 01:00:00.000' AND @dtGMT < '2058-03-31 01:00:00.000' then 1
when @dtGMT >= '2058-03-31 01:00:00.000' AND @dtGMT < '2058-10-27 01:00:00.000' then 2
when @dtGMT >= '2058-10-27 01:00:00.000' AND @dtGMT < '2059-03-30 01:00:00.000' then 1
when @dtGMT >= '2059-03-30 01:00:00.000' AND @dtGMT < '2059-10-26 01:00:00.000' then 2
when @dtGMT >= '2059-10-26 01:00:00.000' AND @dtGMT < '2060-03-28 01:00:00.000' then 1
when @dtGMT >= '2060-03-28 01:00:00.000' AND @dtGMT < '2060-10-31 01:00:00.000' then 2
else NULL
end , @dtGMT ) as dtLT
)
/* effe testen */
Select getUTCdate() as UTCdate, GETDATE() as getdate, dtLT
from master.dbo.fn_ALZDBA_GMT_2_LT_Brussels ( getUTCdate() )
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 24, 2010 at 1:21 pm
thank you Johan.
Looks Like I have options to present to the developer. I do spoil them.
I should have known better and explicitly stated SQL2005 but your code looks quite compatible to me.
'Wat is de correctie ' - I love it, the translation is obvious when it is in context. 🙂
---------------------------------------------------------------------
September 24, 2010 at 1:38 pm
Oh, indeed quick copy / paste, you know ... forgot to translate the comments, but you got it anyway 😎
Off course, IMHO, it is better to avoid this kind of inline conversions, if you know you always need to return local time, store local time (maybe even with an extra GMT column).
GMT will be excellent to avoid duplicate times, but at the cost of what ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 24, 2010 at 3:39 pm
Johan, you are right, as the app is designed to be used globally and is therefore attempting to store the date without including time zone offsets it should store the dates consistently and at least record any amendments made to the date so you can convert back to local time.
Actually maybe it does, I have absolutely no access to the database or application in question. I will ask the developer to confirm that as another option.
The issue arises because end-users now want reports with date values as at the local time when the case was entered. Not an unreasonable request.
---------------------------------------------------------------------
September 25, 2010 at 12:10 am
george sibbald (9/24/2010)
Johan, you are right, as the app is designed to be used globally and is therefore attempting to store the date without including time zone offsets it should store the dates consistently and at least record any amendments made to the date so you can convert back to local time.Actually maybe it does, I have absolutely no access to the database or application in question. I will ask the developer to confirm that as another option.
The issue arises because end-users now want reports with date values as at the local time when the case was entered. Not an unreasonable request.
Indeed, the request is OK.
It's just a matter of trade-off for the implementation.
If the request is to show the local datetime at the actual place the data has been entered / modified, IMO the easiest way is to add the column.
There must have been a reason to store the GMT, which I don't challenge.
However, if the request is "show _my_ local time" and _my_ is relative, meaning someone in Liverpool must see Liverpool time, and another person in a branch in Brussels must see Brussels time, that's only determinable at runtime, then the only choice is to find the fastest runtime solution and place (sql (fn or clr-fn) or app).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 4, 2010 at 4:23 am
thanks for your help guys, the devs went with the option to write a function to determine if a date is within a BST period or not.
---------------------------------------------------------------------
October 4, 2010 at 5:02 am
Thank you for the feedback.
Can you provide some performance data of their tests and chosen solution ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 4, 2010 at 5:35 am
ALZDBA (10/4/2010)
Thank you for the feedback.Can you provide some performance data of their tests and chosen solution ?
er........I doubt it. What exactly were you looking for?
---------------------------------------------------------------------
October 4, 2010 at 5:44 am
Did they benchmark TVF vs scalar functions vs CLR functions for this case ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 4, 2010 at 5:56 am
ALZDBA (10/4/2010)
Did they benchmark TVF vs scalar functions vs CLR functions for this case ?
🙂 ah such an optimist! I do not think so but if it is important to you I can ask.
---------------------------------------------------------------------
October 4, 2010 at 6:30 am
george sibbald (10/4/2010)
ALZDBA (10/4/2010)
Did they benchmark TVF vs scalar functions vs CLR functions for this case ?🙂 ah such an optimist! I do not think so but if it is important to you I can ask.
Arch yes, we persui the optimal world :hehe:
Probably the easy way isn't the most optimal.
If your devs chose the wrong one, your system will pay for it in the long run.
(depending on the execution frequency)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply