BAQ Error : [Conversion failed when converting date and/or time from character string]

Epicor ERP 10
, ,
1

I have a simple BAQ that merely looks at labor records over a specified date range while identifying the employees who clocked into their operations during that time frame.

The table set-up on the BAQ is this:

image

… and the criteria is configured to determine the date range that you might be interested in:

image

I have two (2) calculated fields that determine the “hours of clocked time” on the operation and the remaining “minutes of clocked time” on that same operation, should there be no “full hours” remaining to calculate:

ClockedHrs : (DATEDIFF(n, LaborDtl.DspClockInTime, LaborDtl.DspClockOutTime) / 60)

ClockedMins : (DATEDIFF(n, LaborDtl.DspClockInTime, LaborDtl.DspClockOutTime) % 60)

Problem
The problem is, if my date range is 01/01/2022 to 01/31/2022, I’ll have a good collection of records returned without error. However, if I change that date range to something like, 01/22/2022 to 01/31/2022, then I’ll have the following error:

image

On the server Event Log, it’ll indicate:

Conversion failed when converting date and/or time from character string.

Why would the BAQ allow the dates within the “problem range” to inclusively run within the “wider range” of dates, but not on its own?

I suspect the problem lies in the calculated fields, but I’ve used the same DATEDIFF function on other BAQs in the past and have not seen this error before.

Thanks for helping.

2

I ran into a similar issue (if not the same one). It had to do with ClockInDate being Date but something I was doing elsewhere was DateTime. I concatenated the ClockInDate and ClockInTime to get around the issue. Maybe you can try the same thing. :man_shrugging:t2:

(CONVERT(nvarchar(30),CONCAT(LaborDtl.ClockInDate ,'T',TRY_CONVERT(time, LaborDtl.DspClockInTime)), 126)) as [Calculated_IN_DateTime],
	(CONVERT(nvarchar(30),CONCAT(LaborDtl.ClockInDate ,'T',TRY_CONVERT(time, LaborDtl.DspClockOutTime)),126)) as [Calculated_OUT_DateTime],
3

As it turns out, I can no longer perform a simple DATEDIFF() function between two date fields in the [Erp.LaborDtl] table; else those errors appear (even though my initial date range testing worked extremely well, without fail, even on previous BAQ/SSRS report solutions).

Here’s what eventually worked for me (my goal was to simply calculate clocked hours on an operation; and then separately, the remaining clocked minutes on the same operation):

Instead of:

Hours:
(DATEDIFF(n, LaborDtl.DspClockInTime, LaborDtl.DspClockOutTime) / 60)

Remaining minutes:
(DATEDIFF(n, LaborDtl.DspClockInTime, LaborDtl.DspClockOutTime) % 60)

… the only thing that worked for me was:

Hours:
(DATEDIFF(n, DATEADD(minute, LaborDtl.ClockInMinute, '1953-10-30'), DATEADD(minute, LaborDtl.ClockOutMinute, '1953-10-30')) / 60)

Remaining minutes:
(DATEDIFF(n, DATEADD(minute, LaborDtl.ClockInMinute, '1953-10-30'), DATEADD(minute, LaborDtl.ClockOutMinute, '1953-10-30')) % 60)