Question on Subtraction Date

[geraldhans]

Hi All

I want to seek some inquiries. I try to query to see the days difference between my Document Date and UDF section called BL Date. Here the query I wrote

SELECT DATEDIFF(DAY, $[OINV.U_ETD], $[OINV.U_ETD]) AS 'Lead Time'

So for example if my Document Date is 20th March 2023 the UDF is 23th March 2023, It will calculated the difference automatically. I assigned this FMS on UDF called Lead Time. If this query works, 3 will be displayed. However I was prompted with this errors

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. '' (SWEI)

The main reason why I wrote this Query is I want to generate Due Date on AR Invoice automatically. Yes I know its possible to do this via Payment Terms on Business Partner, however due to unexpected payment terms on some customers we need to this query.

Apprecaite your help

Regards

Gerald

[SonTran]

Hi,

The error come from UDF data type. It is not date type.

You can re-define UDF data type or use cast/convert function, then datediff function will work.

Hope this helps,

Son Tran

[geraldhans]

Hi Son Tran

Thanks for your prompt reply. If I can remember, I set the UDF as Date for $[OINV.U_ETD). Below are the screen caps

You can see the field ETD are on Date format. While I want to display the lead time difference between the Doc Date the UDF called ETD.

[SonTran]

Try this

SELECT DATEDIFF(DAY, $[OINV.DocDate.Date], $[OINV.U_ETD.Date]) AS 'Lead Time'

Hope this helps,

Son Tran

[kothandaraman_nagarajan]

Hi,

Try this,

SELECT DATEDIFF(DAY, $[OINV.DocDate], $[OINV.U_ETD]) AS 'Lead Time'

[geraldhans]

Hi Nagarajan

Thanks for your reply. I think I miswrote the Query above. Yes its supposed to be

SELECT DATEDIFF(DAY, $[OINV.DocDate], $[OINV.U_ETD]) AS 'Lead Time'

However same errors appear as below

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. '' (SWEI)