Dynamically define day of the month

[sashafay]

How can I get dynamically define day of the month? For example get always 10th of each month and use it as part of the report filter, i.e. on February 28th it will return February 10th, on March 5th filter will use February 10th again, but on March 11th, report will be running with March 10th. Can I do this with Cognos built-in function? If not, SQL will work as well.

Hope that clear...  :-\

[PRIT AMRIT]

Create a DATA ITEM as [Current Date]

cast(case when extract(day,current_date) >10
then extract(year,current_date)*10000+extract(month,current_date)*100+10
else extract(year,_add_months(current_date,-1))*10000+extract(month,_add_months(current_date,-1))*100+10
end,date)

In filter: [DATE]=[Current Date]
[DATE] is your table date column.

Does this make sense?

Thanks
Prit

[sashafay]

Yes it does, except small thing: you got me SQL Server syntax and I'm in Oracle shop here. I maybe able to convert it to Oracle SQL, but anyway... Thank you! :D

[PRIT AMRIT]

If Oracle, Instead of Cast you can use to_date... and rest should remain same...