GETUTCDATE returning invalid date

Question

Post reply

GETUTCDATE returning invalid date

longobardia

SSC Eights!

Points: 957
More actions
March 1, 2010 at 10:39 am

#139137

Recently I created a user defined function to return the current SQL Server's UTC. Here is the code:
CREATE function [dbo].[udf_GetServerUTCOffset]
()
RETURNS int
as
begin
declare @utc int
if getutcdate() > getdate()
select @utc = datepart(hh, (getutcdate() - getdate() )) * -1
else
select @utc = datepart(hh, getutcdate() - getdate() )
return @utc
End
When running this udf on a busy server, the results are inconsistent. Here in the East Coast this function returns -5. When exercising this function over 100,000 iterations, I see that this function is returning -4 serveral times. On ocasion it just starts returning -4 without stopping.
Here is the iteration code:
set nocount on
declare @cnt int
declare @utc int
select @cnt = 1
while @cnt < 1000000
Begin
Select @cnt = @cnt + 1
select @utc = dbo.udf_GetServerUTCOffset()
if @utc <> -5
select ' @utc=' + cast(@utc as varchar(20)) + ' at iteration = ' + cast(@cnt as varchar(20))
End
Is this a SQL 2005 server bug or is it the code being used inside the udf? Any responses would be appreciated.
Thanks,
AL

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Curious, why aren't you using DATEDIFF?

select getdate(), getutcdate(), datediff(hh, getutcdate(), getdate());

Lowell SSC Guru Points: 323518 More actions · Answer 2

Al I'm seeing the same thing; i even changed it so that the calculation was in the loop, instead of as a function; i still got -4 offsets sometimes, but i got fewer of them.

with the inline code, i got ~10 errors in 2005, and ~3 errors in 2008; with the function, i seemed to get 3x that number pretty consistently.

declare @cnt int

declare @utc int

select @cnt = 1

while @cnt < 1000000

Begin

Select @cnt = @cnt + 1

if getutcdate() > getdate()

select @utc = datepart(hh, (getutcdate() - getdate() )) * -1

else

select @utc = datepart(hh, getutcdate() - getdate() )

if @utc <> -5

select ' @utc=' + cast(@utc as varchar(20)) + ' at iteration = ' + cast(@cnt as varchar(20))

End

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

longobardia SSC Eights! Points: 957 More actions · Answer 3

I am going to try Lynn's approach to see if that also triggers the problem.

Yes, I also noticed that doing it straight code you would get less problems.

I going to gobble up some lunch and try it later. Let me know if you guys try Lynn's approach.

AL

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Change your code. I ran the following (UTC difference is -7 where I am), no problems:

declare @cnt int

declare @utc int

select @cnt = 1

while @cnt < 1000000

Begin

Select @cnt = @cnt + 1

set @utc = datediff(hh, getutcdate(), getdate())

-- if getutcdate() > getdate()

-- select @utc = datepart(hh, (getutcdate() - getdate() )) * -1

-- else

-- select @utc = datepart(hh, getutcdate() - getdate() )

if @utc <> -7

select ' @utc=' + cast(@utc as varchar(20)) + ' at iteration = ' + cast(@cnt as varchar(20))

End

Lowell SSC Guru Points: 323518 More actions · Answer 5

i used Lynns approach and stuck the results in a temp table;

no -4 offsets at all when i did that:

--create the tmp table

select getdate() As dt, getutcdate() as utcdt, datediff(hh, getutcdate(), getdate()) as diff into #tmp;

--run the loop

declare @cnt int

declare @utc int

select @cnt = 1

while @cnt < 1000000

Begin

Select @cnt = @cnt + 1

insert into #tmp(dt,utcdt,diff)

select getdate(), getutcdate(), datediff(hh, getutcdate(), getdate());

End

--find exceptions

select * from #tmp where diff <> -5

drop table #tmp

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

longobardia SSC Eights! Points: 957 More actions · Answer 6

longobardia

SSC Eights!

Points: 957

March 1, 2010 at 11:10 am

#1126590

Why is that happening?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Now, for the gotcha, not all UTC offsets are hourly. You will find some that have an odd offset, like 12.5 hours. In this case, you may want the offset in minutes instead of hours.

longobardia SSC Eights! Points: 957 More actions · Answer 8

Lynn,

That is for other than the US, unless that has changed recently.

ie: somewhere in south america and some places in Africa use 1/2 hours and 45 minutes.

Al

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

True, but just keep in mind that those never will change requirements always do. Best to be prepared should it occur.

longobardia SSC Eights! Points: 957 More actions · Answer 10

Lynn,

I couldn't agree more. I try to account for as much as I can get my brain around it. But sometime rely on someone else testing to make sure.

Thanks for the datediff tip instead of using dateadd. I get to comfortable using dateadd for almost everthing to do with date calculations.

You are the best!!!

AL