I have recently started working on Cognos Report Studio. had a T-sql code with 4 table joins. I simply pasted the code in Cognos Report Studio by dragging the SQL toolbox to Query Explorer. The report did run successfully. But now I want this report generated on 1st of every month with maturity date falling between 1st to 30th/31st of that month. For Eg: If I get a report on 1st May, It should give records of data where the maturity date range is between 1st may to 31st may. I tried adding the below code to my already written SQL code:
WHERE
CURR_MATURITY_DATE BETWEEN (DATEADD(MM, 0, GETDATE()), 0) AND (DATEADD(MM, 0, GETDATE()) +1, 0) -1)
This code doesn't work. Pl Note: THe format of column CURR_MATURITY_DATE is: mm/dd/yyyy. Please advise what changes are required in the code to run successfully.
Cognos has an add months function. (At least, Cognos 10 does.) In your expression editor, on the functions tab, it's under Business Date/Time Functions. It's called _add_months. So your function would end up being something like between (_add_months(1,current_date))
You can also use the SQL Server function. You have to put those text parameters (like MM) in curly brackets. So you would end up with dateadd({MM},0,current_date).
For the first day of the current month, in TSQL you would use:DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
In Cognos, the syntax would be DATEADD({MONTH}, DATEDIFF({MONTH}, 0, GETDATE()), 0)