Find first and last day of specific months before/after current date using SQL server

My today's blog post is about finding the first and last day of any month from the current day. Today while working on a report project user needed details from last six month to next six months. So I created this query.

DECLARE @FirstDay DATETIME ,
    @LastDay DATETIME ,
    @MonthBackward INT = 6 ,
    @MonthForward INT = 6;

-- First Day of Month which was before @monthbackward
SET @FirstDay = ( SELECT    DATEADD(MONTH,
                                    DATEDIFF(MONTH, 0,
                                             DATEADD(MONTH, -@MonthBackward,
                                                     GETDATE())), 0)
                );
-- Last Day of Month which was after @
SET @LastDay = ( SELECT DATEADD(MONTH, 1,
                                DATEADD(MONTH, @MonthForward, GETDATE())
                                - DAY(DATEADD(MONTH, @MonthForward, GETDATE()))
                                + 1) - 1
               ); 

SELECT  @FirstDay [Day_1_SixMonthBefore] ,
        @LastDay [Day_last_SixMonthAfter];

Comments

Post a Comment