SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 UTC to LOCAL Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Manivannan.kariamal
Starting Member

India
9 Posts

Posted - 03/26/2013 :  04:51:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/26/2013 :  06:10:52  Show Profile  Reply with Quote
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)
Go to Top of Page

Manivannan.kariamal
Starting Member

India
9 Posts

Posted - 03/26/2013 :  06:56:19  Show Profile  Reply with Quote
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.

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/26/2013 :  07:25:07  Show Profile  Reply with Quote
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/
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 03/26/2013 :  08:44:26  Show Profile  Reply with Quote
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.

Edited by - James K on 03/26/2013 08:45:20
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000