what database? If you’re talking DB2, then you can use the following to get the date for 6 months ago :
substring(digits((year(now() – 6 months) * 100 + month(now()- 6 months)) – 190000),6,5)
substring( digits( ((year(now() – 6 months) * 10000 + month(now()- 6 months) * 100 + day(now() – 6 months)) – 9000000)
if you want to return days as well.
If you’re using SQL server, the convert function is easier to deal with.
If you’re using the above against a lot of columns, it may run slow. if it does, create a table in QTEMP, insert the result into the table, and then do a join to the table
Anyone know a better way to handle this? it’s the best I’ve been able to find for dealing with CYYMMDD type dates…