I can’t take credit for this – I was walking down this road and needed to find a way to handle the leading zeros on the cast so I went online, but it’s a nice way to get a current date in a YYYYMMDD format.

select
substr(char(year(current date)),1,4)|| 
case when 
month(current date) <= 9 then '0'||
substr(char(month(current date)),1,1) 
else 
substr(char(month(current date)),1,2) end||
case when 
day(current date) <= 9 then '0'||
substr(char(day(current date)),1,1) 
else 
substr(char(day(current date)),1,2) end 
from SysIBM/SysDummy1

The solution was found at http://www.tek-tips.com/viewthread.cfm?qid=535886&page=85 by user ‘leveyp’. Much thanks to them for posting the suggestion.