How to find XXth day of previous month in SQL server?

Question

I have 2 parameters,

• an input date (DATETIME)
• input day of month (TINYINT)

If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the output DATETIME.

In other words, I would like to select the Xth day of the previous calendar month. I am then going to use DATEDIFF to find the current fiscal day of month.

Answer 1

DECLARE @d DATETIME = '20140311', @dm TINYINT = 26;

SELECT DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d)));

Result:

2014-02-26 00:00:00.000