Link to home
Start Free TrialLog in
Avatar of weimha
weimhaFlag for United States of America

asked on

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.
SOLUTION
Avatar of Rosenet
Rosenet

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of weimha

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial