?
Solved

Update Datetime according

Posted on 2013-05-20
6
Medium Priority
?
193 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
[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 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

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