Basic clock in / clock out data - how does it work

Epicor ERP 10
1

I am seeing 2 numbers for Epicor clock-in and clock-out dates and times. They are 8 digit numbers and seem to represent minutes from a “beginning date”.

Anyone know the secret recipe??? How do these work. I seem to recall they are based on an Epicor “start date” assigned many years ago…
Thanks!

2

in other tables they’ve done this goofy stuff, the date has been January 1st, 1999. In time it’s usually seconds after midnight.

3

I will give that a try… I am pulling data from the “labordtl” table. For instance I get this data:
Labor Entry from 12/3/2015 at 12:00am = 32657760.

4

I know this is super old but… Did you figure this out? I am trying to write records into labordtl and have not figured out the ClockInMinute value.

Thanks,

Ayrin

5

Now this is even older…
I’ve tried to work this out and am struggling. I run the following and it gives a wide range of dates:
select cast(ClockInDate as datetime)+(ClockinTime/24) - cast(ClockInMInute as float)/1440,ClockInDate,ClockinTime,ClockInMInute,* from erp.labordtl order by 2 desc,3 desc

Also, sometimes one ClockInTime will be less than another even though the dates are reversed. There seems no reasoning to the field.

6

I don’t know how I found this. but…
DateTime baseDate = new DateTime(1953, 10, 30);
dtlRow.ClockInMInute = (dWorkDate - baseDate).Days + 480; //8am
dtlRow.ClockOutMinute = (dWorkDate - baseDate).Days + 990; //16:30

No guarantees - but seems to work.

1 Like
7

Was really hoping it was Nov 12, 1955.

:cloud_with_lightning: :clock10: :man_scientist:

8

what is your final aim ?

9

NOTE: You might not care about my collision stuff, I needed that for an API - but this is it in a Nutshell.

Supports DST, Timezone Diffs, Someone working past midnight, etc…

SELECT
    Erp.LaborHed.LaborHedSeq,
    Erp.LaborHed.EmployeeNum,
    Erp.EmpBasic.Name,
    Erp.LaborHed.ClockInDate, 
    Erp.LaborHed.ClockInTime,
    DATEADD(second, Erp.LaborHed.ClockInTime * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime)) AS ClockInDateTime,
    Erp.LaborHed.LunchOutTime,
                             
    DATEADD(SECOND, 
        1, -- Add 1 second to avoid ClockOut and Lunch Collisions
        DATEADD(second, Erp.LaborHed.LunchOutTime * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime)) 
        ) AS LunchOutDateTime,
 
    IIF(Erp.LaborHed.LunchOutTime > Erp.LaborHed.LunchInTime, Erp.LaborHed.LunchInTime + 24, Erp.LaborHed.LunchInTime) AS LunchInTime,
 
    DATEADD(SECOND,
        2, -- Add 2 seconds to avoid ClockOut and Lunch Collisions
        DATEADD(second, IIF(Erp.LaborHed.LunchOutTime > Erp.LaborHed.LunchInTime, Erp.LaborHed.LunchInTime + 24, Erp.LaborHed.LunchInTime) * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime))
    ) AS LunchInDateTime,
 
    IIF(Erp.LaborHed.ClockInTime > Erp.LaborHed.ClockOutTime, Erp.LaborHed.ClockOutTime + 24, Erp.LaborHed.ClockOutTime) AS ClockOutTime,
                             
    DATEADD(SECOND, 
        IIF((Erp.LaborHed.LunchInTime <> 0 and Erp.LaborHed.LunchOutTime <> 0) AND (Erp.LaborHed.LunchInTime <> Erp.LaborHed.LunchOutTime), 3, 1), -- Add 3 or 1 seconds to avoid ClockIn and Lunch Collisions
        DATEADD(second, IIF(Erp.LaborHed.ClockInTime > Erp.LaborHed.ClockOutTime, Erp.LaborHed.ClockOutTime + 24, Erp.LaborHed.ClockOutTime) * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime))
    ) AS ClockOutDateTime,
 
    CASE WHEN (Erp.LaborHed.LunchInTime <> 0 and Erp.LaborHed.LunchOutTime <> 0) AND (Erp.LaborHed.LunchInTime <> Erp.LaborHed.LunchOutTime) THEN 1 ELSE 0 END AS Lunch,
    Erp.LaborHed.PayHours
 
    FROM Erp.LaborHed
        INNER JOIN Erp.EmpBasic ON Erp.EmpBasic.EmpID = Erp.LaborHed.EmployeeNum AND Erp.EmpBasic.Payroll = 1 AND Erp.EmpBasic.Company = Erp.LaborHed.Company
 
    WHERE
        Erp.LaborHed.ClockInTime <> 0.00
        AND Erp.LaborHed.ClockOutTime <>  0.00
        AND Erp.LaborHed.ClockInTime <> Erp.LaborHed.ClockOutTime
 
    ORDER BY
        ClockInDateTime desc, Erp.LaborHed.EmployeeNum
4 Likes
10

Should this not be (dWorkDate - baseDate).Minutes ?
Other than that, it seems to work well.

Or, in SQL:
cast(datediff(minute,cast(‘1953-10-30’ as datetime),ClockInDate)+ClockinTime60 as int) As ClockInMinute
also:
cast(datediff(minute,cast(‘1953-10-30’ as datetime),ClockInDate)+case ClockOutTime when 24 then ClockInTime60 else 0 end +ClockOutTime*60 as int) As ClockOutMinute

Thanks.