How to get the report between certain date range in Cognos

Question

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.

Answer 1

Try these in your WHERE clause:

For first day of the month: select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

For last day of the month : select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

Find it here, it explains all.