[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL Code for Daylight Savings Time?

Posted on 2014-09-19
14
Medium Priority
?
1,380 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
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 2000 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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 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