UTC to LOCAL Date - SQL Server Forums

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

Transact-SQL (2008)

UTC to LOCAL Date

Author

Topic

Manivannan.kariamal
Starting Member

9 Posts

Posted - 2013-03-26 : 04:51:25

Hi,

We have a requirement to pull the data to SQL from MS CRM. MS CRM is storing UTC date in back end and when it displays in the front end, it shows local date. When we pull the data to SQL, we were asked to store the local date as it appears in CRM front end.

We used the below query to convert the date before it gets inserted in our SQL.

dateadd(second,datediff(second,getutcdate(),getdate()),[CreatedOn]) AS [CreatedOn]

it works fine. but we found a problem. records with older date inserted today has incorrect conversion.

Example:

2012-09-26 16:08:59.000

the record which got inserted with this date on september ( where UK GMT offset is +1) is converted as 2012-09-26 17:08:59.000.

but the same record got inserted yesterday was converted same as 2012-09-26 16:08:59.000. this is wrong. CRM is showing the date as 2012-09-26 17:08:59.000

so we need script to convert the date to local time based on that date value not on the current date.

Can you help?

Thanks in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 06:10:52

Means you want to update that CreatedOn column data to +1 minute........
--To update datetime( except for Today ) values to +1 minute
UPDATE TableName
SET CreatedOn = DATEADD( MI, 1, CreatedOn)
WHERE CONVERT( VARCHAR(8), CreatedOn, 112) < CONVERT(VARCHAR(8), SYSDATETIME(), 112)

Manivannan.kariamal
Starting Member

9 Posts

Posted - 2013-03-26 : 06:56:19

Thanks for your reply.

the createdon column is having vaule 2012-09-26 16:08:59.000.

what I want to achieve here is, query that returns 2012-09-26 16:08:59.000 (where UK time is GMT+0) when run today, and 2012-09-26 17:08:59.000 when run in April - October (day light saving where UK time is GMT+1).

the query should consider the day light saving settings from the date supplied. it should not always consider +1.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 07:25:07

Check this link... It will give you idea on Day Light Saving Setting
http://www.mssqltips.com/sqlservertip/1372/daylight-savings-time-functions-in-sql-server/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-26 : 08:44:26

I prefer an approach of having a table of start and end dates of the daylight savings time for all the years of interest for all the regions/locations of interest in a table. Even so, if you want to be completely general, it is a very hard problem for a number of reasons:

1. Daylight savings time is observed by some countries/regions and not by others.
2. Even in countries (such as USA) that observe daylight savigns time, some regions and states (Arizona, parts of Indiana for example) do not observer daylight savings time.
3. The date on which the daylight savings time starts and ends varies by country/region.
4. Even in a given region, the start and end dates is not easily determined via calculations. For example, an act of Congress expanded the daylight savings date range starting in 2007.

In a simplified case, for example, let us say you are interested in only the eastern time zone of USA for 2006 through 2013 you could do this as shown below.

First, create a table of daylight savings start and end times for your region of interest. For USA (excluding Arizona etc.) this would be as follows:

create table dbo.DaylightSavingsDates
(
	yr int,
	region varchar(32),
	startDate datetime,
	endDate datetime
);
insert into dbo.DaylightSavingsDates values
(2006,'USA','20060402','20061029'),
(2007,'USA','20070311','20071104'),
(2008,'USA','20080309','20081102'),
(2009,'USA','20090308','20091101'),
(2010,'USA','20100314','20101107'),
(2011,'USA','20110313','20111106'),
(2012,'USA','20120311','20121104'),
(2013,'USA','20130310','20131103');

Now you can join with that table to find the UTC time as shown below. Here I am hardcoding the offsets. You could calculate that using the diff between local time and UTC time (but be sure to account for whether you are in daylight savings time or standard time when the offset is calculated).

create table #tmpDates(localdate datetime);
insert into #tmpDates values ('2006-04-07T17:03:05.000'),('2013-02-01T11:00:07.000');

declare @stdhours int = 5;
declare @daylighthours int = 4;
select
	t.localDate,
	DATEADD(
		hh,
		case when localdate >= startDate and localdate < endDate then @daylighthours
		else @stdhours end,
		t.localdate
		) as UTCDate
from
	#tmpDates t
	inner join dbo.DaylightSavingsDates d on 
		d.yr = YEAR(t.localdate);

This is not perfect because at 2:00 AM (in the US) when the daylight savings time changes, this will still get confused.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources