SQL Server DATETIME discrepency

Question

We have a very strange problem in date time storing in database. GETUTCDATE function is used in a stored procedure to update a job start time stamp and end time stamp. The stored procedure is called from server side .net code once for start and again to update end time stamp. The problem is 'end time stamp' is earlier than the start time stamp. Similar issue is mentioned in this SO question but it did not help me solve our issue.

Stored procedure code:

    @Id int,
    @Status int

if (@Status = 3) -- Processing
Begin
    Update QUEUE 
    Set STATUS = @Status,
        PROCESSING_START_TIMESTAMP = getutcdate()
    Where ID = @Id
End
Else if (@Status = 4) -- Completed
Begin
    Update QUEUE 
    Set STATUS = @Status,
        PROCESSING_END_TIMESTAMP = getutcdate()
    Where ID = @Id
End 
Else -- Failed
Begin
    Update QUEUE 
    Set STATUS = @Status
    Where ID = @Id
End

From vb.net we call this stored procedure to set start time stamp, process some job and set end time stamp.

      ' Set Status to Processing
      ldbQueueMaster.SaveStatus(liQueueEntryId, 3)

      ' Call the Processor 
      Dim lbCompleted = lQueueProcessor.Processor(lQueueMaster, lsRootPath) 

      ' Set the Status based on Return from the Processor
      If (lbCompleted) Then
        ldbQueueMaster.SaveStatus(liQueueEntryId,4) 'Completed
      Else
        ldbQueueMaster.SaveStatus(liQueueEntryId, 5) 'Failed
      End If

Result from database:

ID             PROCESSING_START_TIMESTAMP       PROCESSING_END_TIMESTAMP
-------------- -------------------------------- ----------------------------
9533789        2016-08-03 18:34:22.190          2016-08-03 18:34:22.187

Any help would be appreciated.

score 0 · Answer 1 · answered Aug 29 '16 at 20:43

You want to make your SP atomic -- transactions should work -- the server should automatically lock the QUEUE table in the transaction -- OR you could lock it by hand before the BEGIN TRANSACTION statement.

BEGIN TRANSACTION

@Id int,
@Status int

If (@Status = 3) -- Processing
 Begin
  Update QUEUE Set
         STATUS = @Status,
         PROCESSING_START_TIMESTAMP = getutcdate()
   Where ID = @Id
 End
Else if (@Status = 4) -- Completed
 Begin
   Update QUEUE Set
          STATUS = @Status,
          PROCESSING_END_TIMESTAMP = getutcdate()
    Where ID = @Id
 End 
Else -- Failed
 Begin
   Update QUEUE Set
          STATUS = @Status
    Where ID = @Id
 End

COMMIT TRANSACTION

score 0 · Answer 2 · answered Aug 29 '16 at 23:10

I think you may be looking in the wrong place. I don't think this is an issue with SQL Server calculating GETUTCDATE() incorrectly, but rather an issue in which the .Net code is not behaving in the same way you are expecting. Without all the methods and whatnot, I can't say what is going on, but I would suggest using SQL Profiler to set up a trace on the proc so you can see exactly how and in what order the calls to the proc are being made. At the very least, that will help you narrow down where the problem lies, whether in SQL Server or in your .Net code.

SQL Server DATETIME discrepency

2 Answers2