Skip to Content

how get the difference between two dates FMS

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 05 at 10:13 AM

    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;
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 05 at 01:04 PM

    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')
    
    Add comment
    10|10000 characters needed characters exceeded