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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Starting Member

9 Posts

Posted - 03/26/2013 :  04:51:25  Show Profile  Reply with Quote

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.


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.

Flowing Fount of Yak Knowledge

2242 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)
Go to Top of Page

Starting Member

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

Flowing Fount of Yak Knowledge

2242 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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 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
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;
		case when localdate >= startDate and localdate < endDate then @daylighthours
		else @stdhours end,
		) as UTCDate
	#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  
 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.03 seconds. Powered By: Snitz Forums 2000