I am querying an AS400 and trying to build a date from separate 2 digit numeric fields Century (WAAPCC) Year (WAAPYY) Month (WAAPMM) and Day (WPAADD). I came up with a wild concat formula that won't work because I don't know how to escape the single quote but here is what I want to work:
MONTH( DATE( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( '"', DIGITS( WAAPMM ) ), '/' ), DIGITS( WAAPDD ) ), '/' ), DIGITS( WAAPYY ) ), '"' ) ) )
I have double quotes now enclosed with single quotes which returns a nice date but when I add the date function I can't filter with it.
First is there a better way and second how do I escape the single quote if there is not?