Solved

SQL Code for Daylight Savings Time?

Posted on 2014-09-19
14
706 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 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Error Log - logging period 1 33
How to SQL Trace a SPECIFIC query 24 58
Max Consumption Rate (MCR) 3 34
Sql Query with datetime 3 12
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now