The datepart second is not supported by date function dateadd for data type date.

In Epicor 10, I was trying to create some SQL to combine the values from the SysDate and SysTime fields of table 'erp.PartTran'. Field 'Sysdate' is of type 'date'. Field 'SysTime' is of type 'int' and is the number of Seconds in to the day, and so will have a maximum values of 86400. So I tried the function:

DATEADD(SECOND,SysTime,SysDate)

But I got the error:

Msg 9810, Level 16, State 1, Line 4

The datepart second is not supported by date function dateadd for data type date.

The fix is to cast the field 'SysDate' to type 'datetime', like this:

DATEADD(SECOND,SysTime,Convert(DateTime,SysDate))

Here is my complete SQL:

SELECT DATEADD(SECOND,SysTime,Convert(DateTime,SysDate)) SystemDate,

TranNum, PartNum, WareHouseCode, BinNum, TranClass, TranType, InventoryTrans, TranQty, UM,MtlUnitCost, ExtCost, CostMethod,

EntryPerson, TranReference, InvAdjSrc, InvAdjReason, LotNum, GLTrans, PostedToGL, MtlMtlUnitCost, EmpID, CostID, ActTranQty, ActTransUOM, BaseCostMethod

FROM erp.PartTran

WHERE Company='ABC' AND PartNum='XYZ'

ORDER BY TranNum;

Reference: What about a new DATEADD function?