How to restore the right date if it was inserted lately

Question

If i have ZKTime machine to register the attendance of the employees .

Sometimes this machine insert bulk of transactions in sql server db with wrong later date like

8-2103 instead of 11-2016

---

---

What are the possible causes of this problem and how to restore to the right date if i can't detect the problem ?

Answer 1

I've looked at the vendor link you supplied and it does not help in this case. I'm afraid we won't be able to answer this due to items outside of SQL Server. I believe you will need to contact Vendor Support for this.

The questions you will need to find out are:

1. How does the time machine calculate the CheckTime data?
2. How does the time machine store the CheckTime data?
3. How does the machine create the file to export to SQL Server?

This appears to be either an issue with how the system records the CheckTime data or in how it either exports / writes the data to SQL server.

As far as correcting the issue a basic update statement will fix it, but since there are different dates you will need to write a unique update for each case.

Answer 2

One possible solution is to make use of a Trigger to validate the date and update the date accordingly. Assuming the table has the Primary Key as id, if a newly inserted row has a date beyond today, it can be reset to the current datetime since employees' attendance record can't be in future.

CREATE TRIGGER CorrectTheDate on Config
FOR INSERT
AS

DECLARE @CT DateTime
DECLARE @id int

SELECT @CT = i.CheckTime FROM inserted i;
SELECT @id= i.id FROM inserted i;

if(@CT >= DATEADD(dd,1,getdate()))
UPDATE MyTable SET CheckTime=getdate() WHERE id=@id

GO