concat columns and then convert to date and finally compare in DB2 (IBM as400)

[Danielht]

I have a db similar to the picture. I want to have the data from three days ago from the current day. I suppose that I have to concat the three columns and then convert to date for finally compare with the current day. I tried this:

select * from MYTABLE where date(concat("-",concat("-",MYTABLE.YEAR,MYTABLE.MONTH),MYTABLE.DAY))>= CURRENT DATE -3

I don't know if it is correct, because I'm connected to IBM as400. Note that data is not similar in their structure.

Help me please

Thanks.

[Thableaus]

Hi @Danielht 

Are your columns of month, day and year strings?

Maybe changing to something like this would work:

date(concat(concat(concat(concat(MYTABLE.YEAR,"-"),MYTABLE.MONTH), "-"),MYTABLE.DAY))

But you need to be working with string to make the concat expression function properly.

Cheers,

[Danielht · Accepted solution]

@Thableaus , I wrote

select *
from MYTABLE
where date(trim(ltrim(YEAR, '0')) || '-' || lpad(trim(MONTH), 2, '0') || '-' || lpad(trim(DAY), 2, '0')) >= current_date - 3 days

and it worked, thanks for your help!