SQL Server get first/last day of previous/current/next month

Posted by quynhnguyen in SQL Server

February 20, 2012

1 Comment

This tutorial demonstrates 2 ways for you how to obtain the first/last day of previous/current/next month in SQL Server by making UDF(User Defined Function) based on basic functions : DATEADD, CONVERT, DAY, MONTH, YEAR.

1. SQL function to get first day of previous/current/next month

Method 1 :

CREATE FUNCTION GetFirstDayOfMonth
(
@inputDate DATETIME,  -- input date
@monthDuration INT    -- (-1 : previous month, 0 : current month, 1 : next month)
)
RETURNS  DATETIME
AS
BEGIN
    DECLARE @retDate DATETIME, @tempDate AS DATETIME 
    SET @tempDate = DATEADD(MONTH, @monthDuration , @inputDate) 
    -- create a date MM/01/YYYY based on @tempDate
    SET @retDate =  CONVERT(DATETIME, CONVERT(CHAR(2), MONTH(@tempDate)) 
                    + '/01/'
                    + CONVERT(CHAR(4), YEAR(@tempDate)))
    RETURN @retDate
END

Method 2 :

CREATE FUNCTION GetFirstDayOfMonth
(
@inputDate DATETIME,  -- input date
@monthDuration INT    -- (-1 : previous month, 0 : current month, 1 : next month)
)
RETURNS  DATETIME
AS
BEGIN
    DECLARE @retDate DATETIME
    SELECT @retDate= DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE) + @monthDuration, 0)
    RETURN @retDate
END

Sample of testing this function :

DECLARE @CurrentDate DATETIME =  GETDATE()  -- Current date
SELECT @CurrentDate AS CurrentDay, 
       dbo.GetFirstDayOfMonth(@CurrentDate,-1) AS FirstDayOfPreviousMonth, -- First day of previous month
       dbo.GetFirstDayOfMonth(@CurrentDate,0) AS FirstDayOfCurrentMonth,   -- First day of current month
       dbo.GetFirstDayOfMonth(@CurrentDate,1) AS FirstDayOfNextMonth	   -- First day of next month

SQL fucntion GetFirstDayOfMonth testing

2. SQL function to get last day of previous/current/next month

Method 1 :

CREATE FUNCTION GetLastDayOfMonth
(
@inputDate DATETIME,  -- input date
@monthDuration INT    -- (-1 : previous month, 0 : current month, 1 : next month)
)
RETURNS  DATETIME
AS
BEGIN
    DECLARE @tempDate AS DATETIME 
    SET @tempDate = DATEADD(MONTH, @monthDuration + 1 , @inputDate) 
    RETURN  (SELECT  DATEADD(dd, -DAY(@tempDate), @tempDate))
END

Method 2 :

CREATE FUNCTION GetLastDayOfMonth
(
@inputDate DATETIME,  -- input date
@monthDuration INT    -- (-1 : previous month, 0 : current month, 1 : next month)
)
RETURNS  DATETIME
AS
BEGIN
    --Takes the first day of this month and subtracts one day from it
    RETURN  (DATEADD(DD, -1, DATEADD(M, 1,[dbo].[GetFirstDayOfMonth] (@inputDate,@monthDuration))))
END

Sample of testing this function :

DECLARE @CurrentDate DATETIME =  GETDATE()  -- Current date
SELECT @CurrentDate AS CurrentDay, 
       dbo.GetLastDayOfMonth(@CurrentDate,-1) AS LastDayOfPreviousMonth, -- Last day of previous month
       dbo.GetLastDayOfMonth(@CurrentDate,0) AS LastDayOfCurrentMonth,   -- Last day of current month
       dbo.GetLastDayOfMonth(@CurrentDate,1) AS LastDayOfNextMonth       --  Last day of next month

SQL function GetLastDayOfMonth testing

Hope it helpful for you !

Tagged: DATEADD SQL Server UDF