Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Code for Daylight Savings Time?

Posted on 2014-09-19
14
Medium Priority
?
1,193 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 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