Solved

Update Datetime according

Posted on 2013-05-20
6
185 Views
Last Modified: 2013-08-03
Hello
we have a web site hosted by a hosting company in USA , our business/customers  in ASIA ... so i have to Add some hours in my code to handle the diffrences between server time and local time .. for example i use the follwoing statement in a  trigger with every insert to get the current local time

    UPDATE  XYZ
    SET     EntryDate = DATEADD(hh, 7, Getdate())
.......

I'm using sql server 2005 Express edition which is hosted by the hosting company as i said above
Is there any way to handle that especially there s a Daylight Savings Time so in summer/winter i have to increase/decrease the hours i added
0
Comment
Question by:ali_alannah
6 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
0
 

Author Comment

by:ali_alannah
Comment Utility
Hi HuaMinChen
Using GETUTCDATE() cant solve the problem , i still have to change the code according the ylight Savings Time
0
 
LVL 8

Expert Comment

by:jpgobert
Comment Utility
When is the datetime value originally logged to the database?  Is it just a matter of tracking visits or is it through some code that you have written into your website?

If the date is being inserted into the database by code you've written in your site then you should definitely always convert to UTC before inserting the value into your database to provide consistency no matter what the end-user's time zone is.

If you're dealing with values that are being logged into the database from some other process or system that you have no control over, and you're just trying to clean up the values after the fact then that's a little different.  First, how are you determining the user's location in order to determine what the time zone offset is to make the adjustment?  Does the adjustment always need to happen in real time?  I'm asking because it sounds like it based on your question... by setting up a trigger on the table it seems you're trying to immediately fix any datetime value that posts....

Ultimately daylight savings time is pretty much on a set schedule each year.  It changed recently due to the President deciding it needed to but that's not common.  You could just write in a conditional statement that looks at the user's location and checks if the date is between the start and end of daylight savings.  If so, add 1hr... if not, continue with your normal update.
0
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.

 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
I mean you can use GETUTCDATE() which is showing the universal time, to detect the difference between server/local time and universal time, like

select GETUTCDATE(),getdate(),datediff(hh,GETUTCDATE(),getdate())
go
0
 
LVL 8

Expert Comment

by:jpgobert
Comment Utility
You might want to check out the following:  Daylight Savings Time Functions in SQL Server

Could be what you're looking for...
0
 
LVL 12

Accepted Solution

by:
sachitjain earned 500 total points
Comment Utility
you could create a single row table with two columns DayLightSavingStart and DayLightSavingEnd. If your date in US server falls within these dates then reduce 1 less hour otherwise reduce normal hours of time difference.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

10 Experts available now in Live!

Get 1:1 Help Now