Issue using DateTime.Now and SQL Servers GetDate() functions

In our project a situation has come up regarding using a combination of .Net’s “DateTime.Now” and SQL Server’s “GetDate()” functions.

In our database structure we have an “Address” table and an “Address_History” table.  Each time a record is saved to the Address table, a trigger writes a new record to the “Address_History” table, giving us a complete history (which is required by our users).  In the “Address_History” table there is a “HistoryWrittenTime” field that we populate using SQL Servers GetDate() function.

Our “get” stored procedures select data out of our “Address” tables.  Now we need to select from our history table for a given HistoryWrittenTime.   Originally we were going to get “current” information from the “Address” table and history information from the “Address_History” table, but then decided to just select from the “Address_History” table and pass the current date time if we want the “current” record.

Everything works find except we sometimes have a situation in our unit testing that occurs because the SQL Server clock might be a half a second faster than the application clock.

In our unit test we do the following:

1.      Write a new record.
2.      Retrieve the record as of the current date time.

When the record is written, the HistoryWrittenTime is set to 2010-04-20 07:40:19.153 (the current time for SQL Server).  When we retrieve the record we pass DateTime.Now as the selection parameter, and the value is 2010-04-20 07:40:19.140 (the current time for the application server).  Since the current time for the application server is slightly behind the current time for the SQL Server we don’t find the record.

To fix the issue for unit testing, we can put delays between the writes and gets, but we are unsure if it will be an issue when running the app in production.

We think we have the following options:
1.      Try to verify that there is enough lag time when running the web application that the difference in time will not be an issue and continue as we are (keeping in mind the situation).
2.      Change to select from our “Address” table if we want “current” data instead of the “Address_History” table. (This is probably the most efficient method.)
3.      Change our save methods to pass the value for the HistoryWrittenTime field from the C# application (DateTime.Now), instead of using SQL Servers GetDate() function.

All of our network devices get the time from the same domain controller but could still be a little bit different from each other.  

Write now, most of the time we need to retrieve the “current” information, but as our project grows it will probably change to be even between the number requests for “current” and “history” data.

We would appreciate any suggestions/comments for our situation.
weimhaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RosenetCommented:
Well one thing you can do is compare parsed dates, so remove the milliseconds (or even seconds) depending on how accurate it needs to be.

A simple list of the date/time functions in SQL can be found here:
http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx

Alternatively you can use a comparison within a few seconds:

Date1 between Date2.AddSeconds(3) and Date2.AddSeconds(-3).  So you're comparing in this case against a range of 5-6 seconds.

Again it depends on how precise you need your comparison to be.  Although I can't say I've personally ever had that problem, are you sure it's the milliseconds causing the discrepancy and not something funny in your code?  
0
cyberkiwiCommented:
Because the clocks are timed only once a day, they can get seconds out of sync.
It is also more robust not to rely on the clocks to be in sync at all.

I would recommend this, which basically puts the app server in control of the time of insert, as well as solving the unit test issue.

3.      Change our save methods to pass the value for the HistoryWrittenTime field from the C# application (DateTime.Now), instead of using SQL Servers GetDate() function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
weimhaAuthor Commented:
We are pretty sure its the clocks being out of sycn because if we move our PC clocks ahead a few seconds it works fine.
0
mastooCommented:
a few other variants...

Your get proc can make the HistoryWrittenTime optional, and return the latest value using some select max logic.

You could have another proc GetAddressLatest that does similar.

And if you want to age data driving a web site without worrying about records getting messed up when you spring forward or fall back (daylight savings time), you might think about aging based on UTC.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Theory

From novice to tech pro — start learning today.

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.