DATEDIFF does not account for daylight savings time shift?

[Kevin Rutherford]

In SQL 7.0, is there a way to get the DATEDIFF function to account for
the daylight savings time shift?

We are using it to determine the number of hours that someone worked.
However, if they started work before the time shift (e.g. '2/1/2000
23:00:00'), and ended work after the "spring forward" time shift (e.g.
'2/2/2000 05:00:00'), DATEDIFF does not seem to recognize the fact that
there was one less hour to account for in that time frame. So it appears
that they worked an extra hour.

Any suggestions?

Thanks

Kevin Rutherford
kruth...@rodeer.com

[Kevin Rutherford]

Oops, my example should have said '4/1/2000 23:00:00' to '4/2/2000 05:00',
but you knew that ... :-)

[BPMargolin]

Kevin,

Pay the person the money for the extra hour now, and shaft him in the fall :-)

SQL Server does not know about daylight savings time. SQL Server simply picks up
the date and time from the operating system. However, depending on how much
sophistication you want to get into, the following might help ...

declare @deltaGMT int
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias', @DeltaGMT OUT
select getdate() as LocalTime, dateadd(minute, @deltaGMT, getdate() ) as GMT

This code, courtesy of Gert Drapers who in turned credits Microsoft PSS,
provides the date and time in Greenwich Mean Time which is independent of
daylight saving time.

Kevin Rutherford <kruth...@rodeer.com> wrote in message
news:38ED1C05...@rodeer.com...

[Roy Harvey]

BP,

>declare @deltaGMT int
>exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
>'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
>'ActiveTimeBias', @DeltaGMT OUT
>select getdate() as LocalTime, dateadd(minute, @deltaGMT, getdate() ) as GMT
>
>This code, courtesy of Gert Drapers who in turned credits Microsoft PSS,
>provides the date and time in Greenwich Mean Time which is independent of
>daylight saving time.

But this only helps when you want to know about right now. When
comparing two dates at least one of them is NOT right now, and so you
do not have enough information to compare them.

Roy

[Roy Harvey]

>But this only helps when you want to know about right now. When
>comparing two dates at least one of them is NOT right now, and so you
>do not have enough information to compare them.

On the other hand, if the column is assigned using getdate(), then
calling this code at the same time as getdate() is called and the GMT
version is stored we CAN make excellent use of this.

Roy

[BPMargolin]

Roy,

I was confident you would come to see that option :-)

BPM

Roy Harvey <roy_h...@snet.net> wrote in message
news:09iseskpleruvhk7h...@4ax.com...