You may across the need to dynamically generate content, while creating reports and dashboards using SSRS (SQL Server Reporting Services). You can pass one of these calculated dates as a default value, when you query a dataset.


To retrieve the first or last day of a given month

First day of current month:

First day of previous month:

First day of next month:

Last day of current month:

Last day of previous month:

Last day of next month:

To retrieve a specific date on a given month:

For Example, to get the 10th of the previous month
=dateadd(dateinterval.month, -1, today().AddDays(-(today().Day-10)))

For Example, to get the 5th of the current month
=dateadd(dateinterval.month, 0, today().AddDays(-(today().Day-5)))

For Example, to get the 20th of the next month
=dateadd(dateinterval.month, 1, today().AddDays(-(today().Day-20)))