dbo.fn_LocalTimeToUTC() function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

dbo.fn_LocalTimeToUTC() function

Author

Topic

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2008-02-12 : 18:46:01

Since you guys were so awesome at answering my first question, I have another one for you:

Currently I'm using the dbo.fn_LocalTimeToUTC() function to convert my date parameters to UTC, which is how time is stored in our database. What I've noticed is this function SIGNIFICANTLY increases the execution time of the query. I'm talking about instead of taking 10-20 seconds to execute a query it now takes 3-5 minutes. Do you guys know of a good way around this? Thanks alot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 18:47:50

Do you return the datetime value as an inline function?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 18:48:36

Or why not use GETUTCDATE() function?

SELECT	GETDATE(),
	GETUTCDATE()

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-12 : 19:49:09

You didn't post the code for your dbo.fn_LocalTimeToUTC() function, the query that you are having trouble with, or the structure and contents of your tables, so it is hard for us to say why it might be a problem.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 19:54:28

I think he uses the fn_UTCToLocalTime found in MS CRM database.
Something similar to

quote:

DECLARE 
    @offset TINYINT, 
    @dt SMALLDATETIME, 
    @sdt SMALLDATETIME, 
    @edt SMALLDATETIME, 
    DECLARE @i TINYINT 
 
SELECT 
    @offset = 5, 
    @i = 1, 
    @dt = '20050201 05:32' 
 
-- find first Sunday in April 
 
WHILE @i < 7 
BEGIN 
    SET @sdt = RTRIM(YEAR(@dt))+'040'+RTRIM(@i) 
    IF DATEPART(weekday,@sdt)=1  
    BEGIN 
        SET @i = 7 
    END 
    SET @i = @i + 1 
END 
 
-- find last Sunday in October 
 
SET @i = 31 
WHILE @i > 24 
BEGIN 
    SET @edt = RTRIM(YEAR(@dt))+'10'+RTRIM(@i) 
    IF DATEPART(weekday,@edt)=1  
    BEGIN 
        SET @i = 24 
    END 
    SET @i = @i - 1 
END 
 
-- subtract hour from offset if within DST 
 
IF (@dt>=@sdt AND @dt<@edt) 
    SET @offset = @offset - 1 
 
SELECT @dt AS CurrentTime, DATEADD(hour, @offset, @dt) AS UTCTime

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-12 : 20:17:30

That function seems like a very bare-bones approach to that conversion. It doesn't account for the hour that daylight savings time starts/ends at, doesn't account for changes from one year to the next, and doesn't allow for different rules in different locations.

CODO ERGO SUM

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2008-02-13 : 15:38:54

Thanks again for your help guys. I was playing around with this function yesterday and did some testing on it, to realize that all it did was add 7 hours to the time parameter. I ended up using dateadd(hh, 7, @startdate) instead to reduce my query running time from 3+ minutes to 20 seconds.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 01:54:54

Great!
What about using GETUTCDATE() instead?

E 12°55'05.25"
N 56°04'39.16"

leviw
Starting Member

1 Post

Posted - 2008-03-17 : 14:44:04

quote:
Originally posted by Skorch

Thanks again for your help guys. I was playing around with this function yesterday and did some testing on it, to realize that all it did was add 7 hours to the time parameter. I ended up using dateadd(hh, 7, @startdate) instead to reduce my query running time from 3+ minutes to 20 seconds.

This would not work when time changes for DST. I ran into the same issue and rather than calling this for every row I created a temporary table (using WITH...AS) and did my comparisons from that. Here is an example:


with Dates as (
  select
    dbo.fn_BeginOfToday(dbo.fn_LocalTimeToUTC(@startDate)) as 
startDate,
    dbo.fn_EndOfToday(dbo.fn_LocalTimeToUTC(@endDate)) as endDate,
)

select
  field1,field2,blahField
from blahTable
where
  blahDate >= (select startDate from dates)
  and blahDate < (select endDate from dates)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources