I have a very odd situation with Dates. My Date formats in m...

[Roger Wilkinson 2]

I have a very odd situation with Dates. My Date formats in my file are I8YYMD. When I run the simple Fex over the file using I8YYMD format. it works great.

By accident I entered a YYMD format while testing something else and it still worked when I had OR in my Where Statement.

Fails on just the first part of the Where Statement. Fails on just the second part of the Where Statement and when I change the OR to an AND in the Where statement it also fails. This is all like it should and is expected but when I have the OR in the Where statement it works and gives no error. I dont understand why. Obviously not an urgent issue just a WTH kind of question.

-SET &TodayDate = &YYMD;

-SET &StartDate = (DATEMOV(&TodayDate,BOM));

-SET &StartDate1 = DATEADD(&StartDate,M,-1);

-SET &DateF = DATEMOV(&StartDate1, BOM);

-SET &FFromDate = DATECVT(&DateF,YYMD,I8YYMD);

-SET &DateT = DATEMOV(&StartDate1, EOM);

-SET &FThruDate = DATECVT(&DateT,YYMD,I8YYMD);

TABLE FILE V9F0413

PRINT

PAYMENT_ID__INTERNAL NOPRINT

PAYEE_ADDRESS_NUMBER

CHECK_ITEM_DATE

VOID_G_L_DATE

CURRENCY_CODE

BY LOWEST G_L_BANK_ACCOUNT

BY LOWEST PAYMENT_ITEM_DOCUMENT_TYPE

BY LOWEST PAYMENT_ITEM_NUMBER

WHERE ( CHECK_ITEM_DATE GE &DateF ) AND ( CHECK_ITEM_DATE LE &DateT )

OR ( VOID_G_L_DATE GE &DateF ) AND ( VOID_G_L_DATE LE &DateT ) ;

ON TABLE SET BYDISPLAY ON

ON TABLE PCHOLD FORMAT XLSX

END

[SATHEESH B]

have you tried adding echo and see what is being passed to the where condition

-SET &ECHO=ALL;

Also did you check CHECK_ITEM_DATE and VOID_G_L_DATE Field format

[Martin Yergeau]

IMHO, I dont even understand how it can gives you a proper result at all.

If you look at the log of your SETs, here what I see

-SET &TodayDate = '&YYMD';

-SET &StartDate = (DATEMOV(&TodayDate,'BOM'));

-SET &StartDate1 = DATEADD(&StartDate,'M',-1);

-SET &DateF = DATEMOV(&StartDate1, 'BOM');

-SET &FFromDate = DATECVT(&DateF,'YYMD','I8YYMD');

-SET &DateT = DATEMOV(&StartDate1, 'EOM');

-SET &FThruDate = DATECVT(&DateT,'YYMD','I8YYMD');

 

-TYPE &TodayDate, &StartDate, &StartDate1, &DateF, &FFromDate, &DateT, &FThruDate

 

&DateF and &DateT are not a date. Not even in I8YYMD format. Its the number of days since 1900-12-31

So how can you perform a WHERE on a date field to a variable wiich is not a date

Also, IMHO mathematically speaking performing test with mixing AND / OR need more parantheses than in you code.

To perform what I thnk you are trying to do, I will code it as below

WHERE ( CHECK_ITEM_DATE GE &DateF AND CHECK_ITEM_DATE LE &DateT ) OR ( VOID_G_L_DATE GE &DateF AND VOID_G_L_DATE LE &DateT ) ;

[Roger Wilkinson 2]

Both Date Fields are I8YYMD. format.

MartinY, Thats the part I am not understanding. It shouldnt work.

When I wrote the fex, I mean to type

WHERE ( CHECK_ITEM_DATE GE &FFromDate ) AND ( CHECK_ITEM_DATE LE &FThruDate)

OR ( VOID_G_L_DATE GE &FFromDate ) AND ( VOID_G_L_DATE LE &FThruDate ) ;

This functions exactly as desired. I get all checks within the month and anything voided within the month.

However I had a phone call while I was writing it and accidentally typed it wrong while not thinking and put the YYMD format yet somehow it still works and gives me the exact same data as when I put the I8YYMD format.

As mentioned, either single part of the Where Statement fails (which I absolutely agree it should) but when its all together as typed in my original post with the OR it works and there is no logical sense as to how or why it does.

[Martin Yergeau]

Basically &YYMD is, in a sence, a I8YYMD format.

You can see it if you do the following in your fex

-SET &ECHO=ALL;

-TYPE &YYMD

-EXIT

Will result in

 

So, yes it will give you the same data set (result).

BUT, I strongly suggest that you change you WHERE sentense to properly have the matching paratheses to group the 4 dates test conditions accordingly with the AND and OR, at least for reading and clarity purpose.

Logically generaly speaking, assuming 4 conditions, your actual test is evaluated:

(((Cond1 is true AND Cond2 is true) OR Cond3 is true) AND Cond4 is true)

Meaning:

1- if Cond1 and Cond2 are both true then Section1 is true

2- If Section1 OR Cond3 is true, then Section2 is true

3- if Section2 is true AND Cond4 is true then the WHERE is true

Which is different than

(Cond1 is true AND Cond2 is true) OR (Cond3 is true AND Cond4 is true)

Meaning:

1- if Cond1 and Cond2 are both true then Section1 is true : does CHECK_ITEM_DATE in the date range

2- If Cond3 and Cond4 are both true then Section2 is true : does VOID_G_L_DATE in the date range

3- if Section1 OR Section2 is true then the WHERE is true : does the record have a date that match either range

But I admit that some DB adapter/language may perform their own way to manage AND / OR condition

[Sarah Buccellato]

Roger, was Martins recent comment helpful

[Roger Wilkinson 2]

This issue was duplicated by support as an issue and has been sent to the development team.