Skip to Content
0

how get the difference between two dates FMS

Mar 05 at 08:27 AM

33

avatar image

Hi experts,

How to get the difference between two dates with fms.

SELECT DAYS_BETWEEN ($[OINV."DocDate"], $[OINV."DocDueDate"]) FROM DUMMY when I run this query I got following errors,

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;303 invalid DATE, TIME or TIMESTAMP value: Error while parsing 05/03/2018 as TIMESTAMP at function __typecast__ 'Kullanıcı tanımlı değerler' (CSHS) () (at pos 21) ,I added query alphanumericfield

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "SELECT": line 1 col 1 '' (UIC1) (at pos 1),I added query numericfield

Do you have any suggestion?

Regards

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Dick Smith Mar 05 at 10:13 AM
0

Try to convert the docdate and duedate as below using TO_DATE function

SELECT DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), TO_DATE('2010-01-05', 'YYYY-MM-DD')) "days between" FROM DUMMY;
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for your answer but still same error.

screenshot-6.png

screenshot-7.png (30.7 kB)
screenshot-6.png (24.8 kB)
0
Danilo Kasparian Mar 05 at 01:04 PM
0

The query Dick Smith provided is correct, in your screenshot you are just missing the correct format

In the TO_DATE function you need to provide the date as the format you want

The format must match the date format you are sending.

e.g.

TO_DATE('2009-12-05', 'YYYY-MM-DD')

or as in your case

TO_DATE('05/12/2009', 'DD/MM/YYYY')
Share
10 |10000 characters needed characters left characters exceeded