Solved

SQL Code for Daylight Savings Time?

Posted on 2014-09-19
14
948 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 48

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 48

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

730 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