1 Reply Latest reply: Oct 24, 2016 10:10 AM by Gysbert Wassenaar RSS

    Expression in where clause on JDE date field

    Chris Hopkins

      Hi all,

       

      i am trying to add a WHERE clause in my script to restrict a date by year. its JDE julian dates and i am using the following

       

      F4111:

      SQL SELECT *

      FROM PRODDTA.F4111 where right(Date(MakeDate(1900+left(text(ILTRDJ),len(ILTRDJ)-3))+right(text(ILTRDJ),3)-1) ,2)=15;

      ;

       

      the expression right(Date(MakeDate(1900+left(text(ILTRDJ),len(ILTRDJ)-3))+right(text(ILTRDJ),3)-1) ,2) returns the final 2 characters of the date, i.e. 15.

      does anyone know why this isnt working?