Solved

SQL Code for Daylight Savings Time?

Posted on 2014-09-19
14
1,056 Views
Last Modified: 2014-09-19
Hello:

One of the fields I have in a report is based on the following T-SQL query:

 DATEADD(s,({*Document_tb:TimeCreated} + (3600*-5)),'1/1/1970')

The customer correctly noted that the resulting "Time Created" is off by an hour.  Specifically, this field displayed as 2:24 PM, when it should have displayed as 3:24 PM.

The customer is in the Eastern Time Zone,  and this field is not changing with the Daylight Savings Time change occurring each spring.  

Anyway, to manually fix this, I had to change the -5 to a -4.

Is there a way to implement some sort of Daylight Savings Time "table" or code, so that I don't have  to keep making this manual adjustment?

 TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40332471
You could perhaps use something like this, comparing to a constant, to calculate the offset you need to subtract or add for the time zone.

SELECT dateadd(mi, datediff(mi,GETUTCDATE() ,getdate())

GETUTCDATE
Returns a datetime value that contains the date and time of the computer on which the instance of is running. The date and time is returned as UTC time (Coordinated Universal Time).
http://msdn.microsoft.com/en-AU/library/ms186724.aspx
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40332473
Take a look at the TSQL type DateTimeOffset. It allows you to specify the offset as a specific amount of hours and minutes (from GMT).  There's a good intro here - far better than I could devise myself!

hth

Mike
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40332486
try using getutcdate ,

Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

http://msdn.microsoft.com/en-us/library/ms178635.aspx
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 1

Author Comment

by:TBSupport
ID: 40332492
Does this work?

SELECT dateadd(mi, datediff(mi,GETUTCDATE() ,TimeCreated)
--where "TimeCreated" is substituted for "getdate()"
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40332510
NO

to start with [TimeCreated] is an integer field based on seconds since 1970-01-01 according to your last question.

this
SELECT dateadd(mi, datediff(mi,GETUTCDATE() ,getdate())
gives a number of minutes between a constant scale (utc time) and the "current" date/time

what does it return for you now?

when Summer time stops it will be 60 minutes different
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40332529
I need it to take into account the "TimeCreated" field.  I can't leave that field out of the equation.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40332534
DATEADD(s,({*Document_tb:TimeCreated} + (3600*-dateadd(hh, datediff(mi,GETUTCDATE() ,getdate()))),'1/1/1970')
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40332552
Hi samo4fun:

Thank you!  The only issue is that I get an error saying that "DATEADD requires three arguments".

John
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40332580
try this:

DATEADD(s,({*Document_tb:TimeCreated} + (3600*-(5-datediff(hh,GETUTCDATE() ,getdate())))),'1/1/1970')

if there is a difference in utc and getdate then it will deduct
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40332596
The error is gone.  But, the result of the calculation is off by 5 hours.  It's  displaying 1:05:48 AM, when it should be displaying 6:05:48 AM.

TBSupport
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40332611
try without 5

DATEADD(s,({*Document_tb:TimeCreated} + (3600*-(datediff(hh,GETUTCDATE() ,getdate())))),'1/1/1970')
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40332613
Sorry i do not have SQL now so  cant check the results..
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40332643
Without the 5, it results in 2:05:48 PM.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40333115
Hi samo4fun:

I got your script to work. I substituted 0 for the five.

Thanks, for your help!  It's really appreciated!

TBSupport
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question