Solved

Update Datetime according

Posted on 2013-05-20
6
192 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

688 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