9

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

enter image description here

enter image description here

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

chillworld
  • 4,207
  • 3
  • 23
  • 50
Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
  • 1
    If the wrong date is inserted, how will you know what the right date is? – mendosi Nov 10 '16 at 12:23
  • @mendosi : i don't know i try through the order of `S_T_NO` to guess the nearest date – Anyname Donotcare Nov 10 '16 at 12:26
  • Looks like a wrong datetime format being used on inserts ... You can't recover that - it's corrupt data. – Narf Nov 14 '16 at 10:38
  • @narf : What may be the cause of this problem ? so later date – Anyname Donotcare Nov 14 '16 at 11:08
  • 1
    @AnynameDonotcare , Time to contact the vendor? – Punter015 Nov 15 '16 at 12:45
  • From the listing you showed above, it looks that each Device, SensorID, is set with different date. for example, Device 114 & 111 date in 2103, Device 1 in 2016 and Device 150 in 2084. You probably need to check the date settings on each Device. – Ahmed Saeed Nov 16 '16 at 16:06
  • Out of interest SQL Server Datetimes are based on number of days since 1900-01-01. Your date of 2103-08-28 is 74383 days since 1900-01-01 does this figure ring any bells? – Steve Ford Nov 16 '16 at 16:56
  • @SteveFord : hmmm , i don't know , could you explain what did you mean – Anyname Donotcare Nov 16 '16 at 17:30
  • @AhmedSaeed : Could you provide more details please, what should i do? – Anyname Donotcare Nov 17 '16 at 04:50
  • 1
    @AnynameDonotcare SQL Server datetimes are based upon the number of days since 1900, so if you SELECT DATEDIFF(day, 0, '2103-08-28') and SELECT DATEDIFF(day, '1900-01-01', '2103-08-28') both will return 74383 as the number of days difference between the two dates, showing that SQL Server Datetime are based upon number of days since 1900. – Steve Ford Nov 17 '16 at 09:08
  • 1
    It looks like not all the sensors have the correct date / time setup (sensor 001 looks like it is OK). you can setup the date and time of the sensors look at this information: http://www.ictproduct.com/2014/04/25/3-1-set-date/ – Steve Ford Nov 17 '16 at 09:15
  • I think you need to check the date on each individual device and simply adjust the date, if not correct. – Ahmed Saeed Nov 18 '16 at 02:08

2 Answers2

4

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.

bwilliamson
  • 391
  • 1
  • 13
1

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
Vicky1729
  • 113
  • 6
  • Although the idea is good, the code require some tweaking because it assumes the trigger will fire for one record at a time, Also, most of these machines can work offline when the server is unavailable due to network disconnection or server maintenance ...etc. in which case, the reader will send the records to the server at times different from the reader actual time. – Ahmed Saeed Nov 16 '16 at 15:39