SQL Server Date Calculation – How to Find XXth Day of Previous Month in SQL Server

datetimesql-server-2008-r2

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.

Best Answer

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

Related Solutions

Sql-server – Performance Tuning for Huge Table (SQL Server 2008 R2)

Upgrade to SQL Server 2012 and use columnstores. They thrive in these requirements. Seriously, download the evaluation edition and give it a try. Drop all indexes, drop the clustered index, simply add a non-clustered columnstore index on all columns and give it a whirl. I've seen cases just like your that reduced the execution time to 2-3 seconds, mostly because of segment elimination kicking in. Some supplemental reads:

SQL Server – How to Find Number of Days Since Beginning of Fiscal Month

This will do it:

DECLARE @d DATE = '20140502', @dm TINYINT = 26;
DECLARE @Start_Of_Fiscal_Month DATE

SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-1, DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, DATEADD(DAY, 1-DAY(@d), @d)));
SELECT @Start_Of_Fiscal_Month
SELECT DATEDIFF(dd, @Start_Of_Fiscal_Month, @d) + 1

Best Answer

Related Solutions

Sql-server – Performance Tuning for Huge Table (SQL Server 2008 R2)

SQL Server – How to Find Number of Days Since Beginning of Fiscal Month

Related Question