Prev: Login Failed Reason: Token-based access failed
Next: [sys.dm_db_index_usage_stats].[user_lookups]
From: Janet on 9 Sep 2009 11:44
Hmmm...I checked the operating system time and it is set correctly for
EST. I also ran the query above but it still returned 11/27. I think I
am getting had by the Daylight Savings Time logic because the
timestamp is shows 23:00 hours, so the conversion would still
calculate midnight and therefore return the wrong date - 11/27. Anyone
know how to either
1) Strip the timestamp off so I just have a date and therefore allow
the conversion to be correct?
2) Accommodate for daylight savings time in the conversion?

Any help would be appreciated. Users don't realize how complicated
these calculations can be!!

From: Tom Cooper on 9 Sep 2009 13:18
One way is to build a table containing the offset to add to the UTC time for
each date range. As long as you only have to deal with eastern time, this
table will be small (2 rows for every year you must deal with). You could
build this table either by hand if it's only a small number of years, or
with a loop, but remember, the rules for when dayolight savings time have
changed several times (for example, the rule changed in 2007). So you could
do something like:

Create Table UTCToEastern (StartDate datetime, EndDate datetime,
OffsetMinutes int,
Constraint PKUTCTOEastern Primary Key (StartDate, EndDate));
Insert UTCToEastern (StartDate, EndDate, OffsetMinutes)
Select '20060402 06:00:00.000', '20061029 07:00:00.000', -240
Union All Select '20061029 07:00:00.000', '20070311 06:00:00.000', -300
Union All Select '20070311 06:00:00.000', '20071104 07:00:00.000', -240
Union All Select '20071104 07:00:00.000', '20080309 06:00:00.000', -300
Union All Select '20080309 06:00:00.000', '20081102 07:00:00.000', -240
Union All Select '20081102 07:00:00.000', '20090308 06:00:00.000', -300
Union All Select '20090308 06:00:00.000', '20091101 07:00:00.000', -240
Union All Select '20091101 07:00:00.000', '20100307 06:00:00.000', -300;

Then just lookup the correct row and add the OffsetHours to the time.
Something like

With cte As
(Select DateAdd(s, d.sessdate, '19700101') as UTCSessionDate
From dbo.view_mdl_attendance_sessions d)
Select DateAdd(mi, (Select u.OffsetMinutes From UTCToEastern u
Where c.UTCSessionDate >= u.StartDate And c.UTCSessionDate < u.EndDate),
c.UTCSessionDate)
From cte c;

Tom

"Janet" <ckauvar(a)gmail.com> wrote in message
news:3e916d8d-2bb1-47c7-ae46-9db05d3f62bc(a)38g2000yqr.googlegroups.com...
> Hmmm...I checked the operating system time and it is set correctly for
> EST. I also ran the query above but it still returned 11/27. I think I
> am getting had by the Daylight Savings Time logic because the
> timestamp is shows 23:00 hours, so the conversion would still
> calculate midnight and therefore return the wrong date - 11/27. Anyone
> know how to either
> 1) Strip the timestamp off so I just have a date and therefore allow
> the conversion to be correct?
> 2) Accommodate for daylight savings time in the conversion?
>
> Any help would be appreciated. Users don't realize how complicated
> these calculations can be!!
>

From: Erland Sommarskog on 9 Sep 2009 18:26
Janet (ckauvar(a)gmail.com) writes:
> Hmmm...I checked the operating system time and it is set correctly for
> EST. I also ran the query above but it still returned 11/27. I think I
> am getting had by the Daylight Savings Time logic because the
> timestamp is shows 23:00 hours, so the conversion would still
> calculate midnight and therefore return the wrong date - 11/27. Anyone
> know how to either
> 1) Strip the timestamp off so I just have a date and therefore allow
> the conversion to be correct?
> 2) Accommodate for daylight savings time in the conversion?
>
> Any help would be appreciated. Users don't realize how complicated
> these calculations can be!!

If you are on SQL 2008, you can do:

select switchoffset(convert(datetimeoffset,
DATEADD(s, 1259294400, '19700101')), '-05:00')

But you still need to know the offset, that is you were on DST or
not on that date. And as Tom mentioned, you may have to account for
the recent DST changes in the US.

If you are on SQL 2005, CLR functions may be the best bet. Adam Machanic
covers this in detail in his "Expert SQL Server 2005 programming" on
Apress.


--
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

First  |  Prev  | 
Pages: 1 2
Prev: Login Failed Reason: Token-based access failed
Next: [sys.dm_db_index_usage_stats].[user_lookups]