?
Solved

Update Datetime according

Posted on 2013-05-20
6
Medium Priority
?
199 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 11

Expert Comment

by:HuaMinChen
ID: 39180326
0
 

Author Comment

by:ali_alannah
ID: 39180333
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
ID: 39180336
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39180354
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
ID: 39180360
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 1500 total points
ID: 39180370
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

807 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