How to Translate UTC dates to local Dates?

Question

Post reply

How to Translate UTC dates to local Dates?

sqlnewby

Right there with Babe

Points: 797
More actions
August 3, 2006 at 7:53 am

#173533

Hello
Is there any function in sql server which converts the dates(UTC) to locat time using either sql stored procedure or functions.
problem:
I have let say 20 users and each work in different timezones and, each user should be able to see their data in their timezone where ever they go and the rest will view in their respective timezones.
i don't know what is the best way to do this.
I am storing the dates in uST format and storing the timezone information for each user in the table against the user and thought this was the best way to do this.
i am stumped with the daylight saving timings as well.
can some one please let me know whether any UDF function to convert this dates.
I don't want to read Timezone information from registry as users might go to different timezones and still expect the data to be viewed in their original timezone
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Couple of ways you can do this however what type of application do they use to access the server.

Is it web based or a desktop application?

sqlnewby Right there with Babe Points: 797 More actions · Answer 2

Thanks for the reply. It is a web based application.

Thanks

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 3

One way to do this is to pass the local date and time from the client to a stored procedure on the server where the data lives...

This should give you the correct offset...

SELECT (@pLocalDateTimeFromClient-GETUTCDATE())

This should give you the corrected time from a column in a table...

SELECT someutcdatecolumn + (@pLocalDateTimeFromClient-GETUTCDATE())

FROM sometable

... if the client clock is taken care of for daylight savings time as it usually and automatically is, DST shouldn't be a problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnewby Right there with Babe Points: 797 More actions · Answer 4

Hi Jeff Moden

Thanks for looking in to my problem.

This is perfect if the client wants to see the dates in their local time but these timezones are fixed for each user so shouldn't change when the user move to another location unless they change their timzone via their application.

I am storing the UTC offset for each user in the table, but the problem is how do we calculate the daylight saving time on a perticular date range

here is my table structure

Users

-------

UserId TimeZoneId

1 1

2 1

3 2

4 3

TimeZones

---------

TimeZoneId TimeZoneName Offsetinminutes

1 GMT 0

2 PST -480

3 EST 600

suppose if i have made some appointments with these users, they should display in their corresponding timezones irrespective of where ever they go.

generally what they want to do is they want to see their appointments on a perticular date range(let's say appointments over a week or a month etc)

so in order to show them the correct timings in their appointments we must take care of the daylight saving time as well

so hope i am clear in what i am looking for. sorry for any earlier confusion if i created in explaining my problem.

Thanks

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 5

Ok, not a problem (hopefully I understand your problem now)...

You'd lookup the timezone for each user, get the offset in minutes and, using DATEADD, simply add the minutes to the UTC datetime...

DATEADD(mi,someutcdatecolumn,OffSetInMinutes)

Is that what you want?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnewby Right there with Babe Points: 797 More actions · Answer 6

is there any way we will know when will be the daylight saving time as we need to adjust the ustoffset for daylight saving time.

thanks

Teague Byrd Right there with Babe Points: 752 More actions · Answer 7

Hi,

Here is a function I wrote to convert from UTC to CentralTime.

It was never fully tested since they abandoned the process so you will need to verify anything you use. I believe my solution below works for figuring out the current daylight savings start and end dates. Basically, you know it starts on the first Sunday in April. Since there can only be one Sunday in the first 7 days of April, if I look at each of the seven days, one and ONLY one of them is going to be Sunday. Keep in mind the powers that be are talking about extending daylight savings time sometime in the next couple of years.

Hope this helps some.

Teague

CREATE function dbo.fn_UtcToCentralTime(@DateToConvert Char(19))

-- Takes a UTC date stored as char(19) e.g. '2004-11-12 13:25:00' and converts it to Central time.

-- Daylight Saving time is taken into account in the calculation.

--

returns smalldatetime

as

begin

declare @YearPart char(4)

declare @FirstSundayInApril smalldatetime

declare @LastSundayInOctober smalldatetime

declare @Offset smallint

declare @CentralTime smalldatetime

set @YearPart = substring(@DateToConvert,1,4)

--Determine the beginning and ending dates for Daylight Saving Time for the year being converted

--This should work for any date including dates in the past and future that can be stored as a SQLServer SmallDatetime

select @FirstSundayInApril =

case

when datepart(dw,@YearPart + '-04-01') = 1 then @YearPart + '-04-01 02:00:00'

when datepart(dw,@YearPart + '-04-02') = 1 then @YearPart + '-04-02 02:00:00'

when datepart(dw,@YearPart + '-04-03') = 1 then @YearPart + '-04-03 02:00:00'

when datepart(dw,@YearPart + '-04-04') = 1 then @YearPart + '-04-04 02:00:00'

when datepart(dw,@YearPart + '-04-05') = 1 then @YearPart + '-04-05 02:00:00'

when datepart(dw,@YearPart + '-04-06') = 1 then @YearPart + '-04-06 02:00:00'

when datepart(dw,@YearPart + '-04-07') = 1 then @YearPart + '-04-07 02:00:00'

end

select @LastSundayInOctober =

case

when datepart(dw,@YearPart + '-10-31') = 1 then @YearPart + '-10-31 02:00:00'

when datepart(dw,@YearPart + '-10-30') = 1 then @YearPart + '-10-30 02:00:00'

when datepart(dw,@YearPart + '-10-29') = 1 then @YearPart + '-10-29 02:00:00'

when datepart(dw,@YearPart + '-10-28') = 1 then @YearPart + '-10-28 02:00:00'

when datepart(dw,@YearPart + '-10-27') = 1 then @YearPart + '-10-27 02:00:00'

when datepart(dw,@YearPart + '-10-26') = 1 then @YearPart + '-10-26 02:00:00'

when datepart(dw,@YearPart + '-10-25') = 1 then @YearPart + '-10-25 02:00:00'

end

--Calculate the offset for the Central timezone

if @DateToConvert > @FirstSundayInApril and @DateToConvert < @LastSundayInOctober

set @Offset = 5 --Central daylight saving time

else

set @Offset = 6 --Central standard time

--Subtract the offset from the given UTC date

set @CentralTime = dateadd(hh,-@Offset,@DateToConvert)

-- Return the calculated Central Time for the given UTC date

Return (@CentralTime)

end

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 8

Nicely done, Teague... and now I (finally) understand what SQLNewBy is trying to do...

The following function will add one hour to any datetime (UTC's usually) passed to it that falls on or between the first Sunday of April and the last Sunday of October at 2:00 AM regardless of what the DATEFIRST parameter is set to... it falls into the general category of "PFM"...

CREATE FUNCTION dbo.AltUTCDateForDST (@UTCDateTime DATETIME) RETURNS DATETIME AS BEGIN DECLARE @DstStartYr DATETIME DECLARE @DstEndYr DATETIME SET @DstStartYr = '04/01/'+STR(YEAR(@SomeUTCDateTime),4) SET @DstEndYr = '11/01/'+STR(YEAR(@SomeUTCDateTime),4) --Trust me on the 11/01 thing SELECT @UTCDateTime = DATEADD(hh,1,@SomeUTCDateTime) WHERE @UTCDateTime BETWEEN (15-@@DATEFIRST-DATEPART(dw,@DstStartYr))%7 + @DstStartYr+'02:00' AND (15-@@DATEFIRST-DATEPART(dw,@DstEndYr ))%7 + @DstEndYr +'02:00'-7
RETURN @UTCDateTime END

Use it in conjuction with your user lookup of the time offset to determine the correct local time for each user.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnewby Right there with Babe Points: 797 More actions · Answer 9

Hi Teague Byrd and Jeff Moden

Thanks for taking time to put these functions and these will work without any doubt for EST or PST but

If i understood correctly what Teague Byrd said, i should store all the daylight saving time start and end dates for all the timezone(these will change year by year i hope)in to my database and had to calculate for each timezone

notes from Tegaue Byrd:

--Determine the beginning and ending dates for Daylight Saving Time for the year being converted

--This should work for any date including dates in the past and future that can be stored as a SQLServer SmallDatetime

Thanks

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 10

Having a calendar table would certainly assure correctness. If you can do that, that would probably be the best way to go...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnewby Right there with Babe Points: 797 More actions · Answer 11

Thanks Jeff Moden,

That is a big ask from me, but still i will give it a go. But still i hope i am far from writing any good stored procedure for this.

Thanks

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 12

Do a search for "aux calendar table" and "auxillary calendar table" on this site and Google... you'll find lot's of examples on how to make a calendar table. Most things about Calendar Tables written by a fellow named "Adam Mechanic" are (although a bit dated now) very well written and very practical.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sjkln SSC Rookie Points: 27 More actions · Answer 13

This version takes what was posted before and adjusts for the change in Daylight Saving Time rules, which become effective in 2007. Also, 2am Eastern time is actually 7am UTC time, so some code was added to adjust the starting DST time based on the timezone. This version below is set up to run for mutiple U.S. time zones, and accounts locations that may not observe Daylight Saving Time (like Puerto Rico).

create function dbo.UTCtoLOCALtime

(

@UTCDateTime DATETIME

) RETURNS DATETIME

AS

BEGIN

DECLARE @DstStartYr DATETIME

DECLARE @DstEndYr DATETIME

DECLARE @Offset INT

DECLARE @Dst BIT

DECLARE @TimeStart DATETIME

DECLARE @TimeEnd DATETIME

-- Retrieve location timezone configuration parameters from database

table tblRefTimeZone (two columns)

-- DST (bit) = does location observe Daylight Saving Time

-- Offset (int) = time offset from UTC to location time zone

SELECT TOP 1 @Dst = DST, @Offset = Offset FROM tblRefTimeZone

-- If the location does not observe Daylight Saving Time,

then adjust for time zone only

IF ((@Dst = 0) or (@Dst is null))

RETURN DATEADD(hh,@Offset,@UTCDateTime)

-- Set UTC start & end times so that they equates to the start of Daylight

Saving Time in the respective time zone

SET @TimeStart = CAST('02:00:00' as datetime)

SET @TimeStart = DATEADD(hh,abs(@Offset),@TimeStart)

SET @TimeEnd = CAST('01:59:59' as datetime)

SET @TimeEnd = DATEADD(hh,abs(@Offset),@TimeEnd)

-- Determine DST Start and End Date

IF year(@UTCDateTime) < 2007

-- Prior to 2007, DST starts on first Sunday in April and ends on last sunday in October

BEGIN

SET @DstStartYr = '04/01/'+ STR (YEAR(@UTCDateTime),4)

SET @DstEndYr = '11/01/'+ STR (YEAR(@UTCDateTime),4)

SELECT @UTCDateTime = DATEADD(hh,1,@UTCDateTime) WHERE @UTCDateTime

Between (15 - @@DATEFIRST - DATEPART(dw,@DstStartYr)) % 7 + @DstStartYr + @TimeStart And

(15 - @@DATEFIRST - DATEPART(dw,@DstEndYr)) % 7 + @DstEndYr + @TimeEnd - 7

END

ELSE

-- Starting in 2007, DST starts on second Sunday in March and ends on first sunday in November

BEGIN

SET @DstStartYr = '03/01/'+ STR (YEAR(@UTCDateTime),4)

SET @DstEndYr = '11/01/'+ STR (YEAR(@UTCDateTime),4)

SELECT @UTCDateTime = DATEADD(hh,1,@UTCDateTime) WHERE @UTCDateTime

Between (15 - @@DATEFIRST - DATEPART(dw,@DstStartYr)) % 7 + @DstStartYr + @TimeStart + 7 And

(15 - @@DATEFIRST - DATEPART(dw,@DstEndYr)) % 7 + @DstEndYr + @TimeEnd

END

-- Adjust time for the location timezone and return value

RETURN DATEADD(hh,@Offset,@UTCDateTime)

END

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 14

VERY nicely done... VERY easy to read and the comments are just right... What do the contents of the tblRefTimeZone look like and where do folks get that info from? That seems to be one of the keys to this code...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)