• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Update Datetime according

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

    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
1 Solution
HuaMinChenBusiness AnalystCommented:
ali_alannahAuthor Commented:
Hi HuaMinChen
Using GETUTCDATE() cant solve the problem , i still have to change the code according the ylight Savings Time
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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

HuaMinChenBusiness AnalystCommented:
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())
You might want to check out the following:  Daylight Savings Time Functions in SQL Server

Could be what you're looking for...
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now