snippetMinor
How to find XXth day of previous month in SQL server?
Viewed 0 times
previoushowsqlxxthmonthfindserverday
Problem
I have 2 parameters,
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.
- 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.
Solution
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.000Code Snippets
DECLARE @d DATETIME = '20140311', @dm TINYINT = 26;
SELECT DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d)));2014-02-26 00:00:00.000Context
StackExchange Database Administrators Q#74975, answer score: 7
Revisions (0)
No revisions yet.