CODEWITHSUNDEEP
sqldatetimeprecisiongetdate
user1769667
user1769667

Reputation: 339

SQL getdate() precision

I have a table which maintains the time entered and time left of various controls within an application. If a user enters a parent control a time entered record is inserted into the table with a null leave time. If a user then enters a child control within it, the parent leave time is update from null and the time entered for the inner control is written in a new record with a null time left value. The time spent within particular areas of the application we are running is crucial. The problem is that the entered time is for the inner control is being recorded as before the time left is being updated for the outer control. The table to maintain time is:

CREATE TABLE [dbo].[tTimeCapture](
    [RECNUM] [int] IDENTITY(1,1) NOT NULL,
    [Store_ID] [int] NULL,
    [Tab_ID] [int] NULL,
    [Dept_ID] [int] NULL,
    [SubDept_ID] [int] NULL,
    [Enter] [datetime] NULL,
    [Leave] [datetime] NULL,
    [IsStoreCall] [bit] NOT NULL,
    [Upload] [int] NOT NULL,
 CONSTRAINT [PK_tTimeCapture] PRIMARY KEY CLUSTERED 
(
    [RECNUM] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This proc Updates leave time of outer control and inserts record for inner control:

 if exists (
      select
         *
      from
         tTimeCapture
      where
         Leave is null
   )
   begin
      exec sEndTimeCapture
   end

   INSERT INTO 
      tTimeCapture(
         Store_ID, 
         Tab_ID, 
         Dept_ID, 
         SubDept_ID, 
         Enter, 
         Leave,
         IsStoreCall, 
         Upload
      )
   SELECT 
      @Store_ID as Store_ID,
      @Tab_ID as Tab_ID,
      @Dept_ID as Dept_ID,
      @Subdept_ID as SubDept_ID,
      GETDATE() as Enter,
      null as Leave,
      @isStoreCall as IsStoreCall,
      0 as Upload

The sEndTimeCapture proc is as follow:

 update
         tTimeCapture
      set
         Leave = getdate()
      where
         Leave is null

When the outer control is entered the first proc is called inserting a new record with a null leave time. The outer control is left and the inner control is entered and the proc updates the outer control leave time to getdate() within the second proc and after that it inserts a new records for the inner control with a null leave value. Every so often, the enter time for the inner control is before the leave time of the outer control. How can this be? Is it a problem with getdate() precision? The time Left of the outer control was: 2013-10-21 12:20:36.753 and time entered for the inner control was 2013-10-21 12:20:36.750

Upvotes: 1

Views: 3044

Answers (1)

Tilak
Tilak

Reputation: 30698

You should use SYSDATETIME which has more precision than GetDate.

In addition change the datetime type to datetime2 for enter/leave date fields.

Difference between DateTime and DateTime2

GetDate vs SysDateTime

Upvotes: 1

Related Questions