SQL need help on WHERE clause

[thelearner]

Hi,

I have an SQL, original condition on the WHERE clause was to get the data only when date1 is greater than date2. Date1 comprises of 4 fields, century, year, month and day. Date2 comprises of 2 fields century and YYMMDD.
WHERE DIGITS(CC) || DIGITS(YY) || DIGITS(MM) || DIGITS(DD)
> DIGITS(CENT) || DIGITS(YYMMDD)
Now I have to change the condition to get the data when date1 > date2 by more than 1 day. ie. if date1 is 4/18 and date2 is 4/17, I should not read this record. But if date2 is 4/16, then I want to read this record. Can anyone help me construct this WHERE clause.

Thanks in advance

 

[Mercury2]

Hi rapeek,
Try out this:

WHERE 
   DAY(DIGITS(CC)|| DIGITS(YY)|| '-'
                 || DIGITS(MM)|| '-'
                 || DIGITS(DD)) - 

   DAY(DIGITS(CENT)||SUBSTR(DIGITS(YYMMDD, 1, 2))||'-'
                   ||SUBSTR(DIGITS(YYMMDD, 3, 2))||'-'
                   ||SUBSTR(DIGITS(YYMMDD, 5, 2))  > 1

HTH -- Philippe

 

[thelearner]

Hi,

I think what you suggested only work if both dates are in the same month. ie. the different between 20040331 and 20040401 is only 1 day. If i use substraction then it would come out 70 days.

 

[Mercury2]

The 2nd day function is useless and wrong.
I think this is the right code.

WHERE 
DAY(
(DIGITS(CC)||DIGITS(YY)||'-'||DIGITS(MM)|| '-'||DIGITS(DD)
- (DIGITS(CENT)||SUBSTR(DIGITS(YYMMDD, 1, 2))||'-'
||SUBSTR(DIGITS(YYMMDD, 3, 2))||'-'
||SUBSTR(DIGITS(YYMMDD, 5, 2)) )  > 1

hope I don't miss a parenthesis.

 

[iSeriesRules]

Perhaps you need to be casting the strings as date fields...
[tt]
WHERE CAST(
DIGITS(CC)||
DIGITS(YY)||'-'||
DIGITS(MM)||'-'||
DIGITS(DD) as date) + 2 days >
CAST(
DIGITS(CENT)||
SUBSTR(DIGITS(YYMMDD), 1, 2)||'-'||
SUBSTR(DIGITS(YYMMDD), 3, 2)||'-'||
SUBSTR(DIGITS(YYMMDD), 5, 2)) as date)
[/tt]

... and then you can use the date arithmetic functions.
Good Luck.


iSeriesRules

 

[thelearner]

Thank you both of you. I tried Talkturkey's way except I added 's' at 'DAYS(...', both places. It worked fine.