Get First Day and Last Day of Month – SQL Server

One of the features i love about SQL Server is function supports. Divide your task in functions just like other programming language. In this article i am going to show you that how to display the First Day and Last Day of Month in SQL Server.

Create a below function to get the First Day of Month:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

-- Return the result of the function
RETURN @Result

END
GO

Now, Create Below Function to get the Last day of month.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))

-- Return the result of the function
RETURN @Result

END
GO

To get the values from function, run below script

select dbo.GetFirstDay(GETDATE()) as StartDate
select dbo.GetLastDay(GETDATE()) as EndDate

remember that to call function we have to call like dbo.functionName