Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joshp
Starting Member
8 Posts |
 Posted - 2005-02-25 : 17:16:14
|
| HI All, I am trying to implement a form of optimistic concurrency using ado.net and comparing a datetime stamp to the datetime in the database (i.e. only update row if datetime is exactly the same).Basically, I get data from a row including the datetime stored in the datetimefield. Upon update, I compare the datetime with the database row to see if anyone else has updated the record before I submit my changes.Here is the T-SQL.UPDATE Changelog SET EntryText = 'Testing the test''er boo bah roxy', CreatedBy = 'JoshP', CreatedOn = '2/23/2005 11:16:28 AM', LastEditedBy = 'JoshP', LastEditedOn = getdate() WHERE Id = 1002 AND LastEditedOn = '2/25/2005 1:56:29 PM'I have confirmed that a row does exist in the Changelog table with an Id = 1002 and a LastEditedOn datetime = 2/25/2005 1:56:29 PM.Query analyzer returns "(0 row(s) affected)" when I try and update using the T-SQL above.What am I doing wrong? Any help would be greatly appreciated. Thanks,JP |
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-02-25 : 17:31:05
|
| Since you are setting the LastEditedOn column using GetDate(), you need to either:a) Add milliseconds to the date in the where clauseb) Change the update to truncate milliseconds off the GetDate() when setting LastEditedOn. |
 |
|
|
joshp
Starting Member
8 Posts |
Posted - 2005-02-25 : 17:34:51
|
| PW, thanks for the help.How would I go about:1. Adding Milliseconds to LastEditedOn in WHERE clause (or)2. Remove the Milliseconds from getdate()Thanks,JP |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-02-25 : 17:45:25
|
| See BOL on the CAST/CONVERT functions. You need to make a design decision as to the granularity of the dates/times in LastEditedON - either it does or doesn't store milliseconds. |
|
|
|
|
|
|
SQL datetime in WHERE clause