Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Danielht
7 - Meteor

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.

 

Captura.PNG

 

Help me please

Thanks.

 

 

 

2 REPLIES 2
Thableaus
17 - Castor
17 - Castor

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
7 - Meteor

@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!

Labels