Effective date statement where date spans multiple columns

Question

Hi -
I'm working on a DB2 database and trying to get records by effective date. The only catch is the effective date fields are spanned across 4 columns (month, day, century, year). I think I have the date piece figured out in the select but when I add the where clause I'm having problems. (note that I'm using the digits command to pad because the year 2005 yields just 5 in the year field)

select date(concat(digits(vsmo),concat('/',concat(digits(vsdy),      
concat('/',concat(digits(vsct),digits(vsyr)))))))  from 
ddpincgr d
where (SELECT MAX(<NOT SURE WHAT TO PUT IN HERE>) FROM ddpincgr a WHERE a.vgrno = d.vgrno) <= date('1/1/2000')

Ideas?

Answer 1

Can't you just put the entire concatenation in the select?

select  date(concat(digits(vsmo),concat('/',concat(digits(vsdy), concat('/',concat(digits(vsct),digits(vsyr)))))))  
from    ddpincgr d
where   ( SELECT MAX(date(concat(digits(vsmo),concat('/',concat(digits(vsdy), concat('/',concat(digits(vsct),digits(vsyr)))))))) 
          FROM ddpincgr a 
          WHERE a.vgrno = d.vgrno) <= date('1/1/2000')

Answer 2

Turn it into a sub-query

select  *
from (select date(concat(digits(vsmo),concat('/',concat(digits(vsdy),      
concat('/',concat(digits(vsct),digits(vsyr))))))) as myDate  from 
ddpincgr d) as myTable
where max(myTable.myDate) <= date('1/1/2000')