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

[dpetet (Dave Petet)]

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!

[rbucek (Rob Bucek)]

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

[dpetet (Dave Petet)]

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.

[ayrinhilbert (Hilbert)]

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

[AndrewM (Andrew)]

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.

[ayrinhilbert (Hilbert)]

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.

[JasonMcD (Jason McDermott)]

Was really hoping it was Nov 12, 1955.

[A.Baeisa (Al)]

what is your final aim ?

Time Interval / Duration BAQ E9.05

@Mi3kel @gpayne @Nancy_Hoyt, i do not know if this will help to simplify what you want or not, and i have noticed that you are not taking into consideration the date change, and i think this is important when working on the LaborDtl table, so i created this SQL equation to calculate the difference in minutes time for each transaction between ClockInDateTime and CurrentDateTime, this one equation will take into consideration the different in days as well as time, and can be altered to any other …

[hkeric.wci (Haso Keric)]

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

[AndrewM (Andrew)]

@ayrinhilbert

(dWorkDate - baseDate).Days

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.