Last Day of the Month

Below is the syntax used to create an extremely simple scalar function to return the last day of the month for the date passed in.

CREATE FUNCTION dbo.fnLastDayOfMonth (@Date DATE)
 
RETURNS DATE
 
AS
 
BEGIN
 
     SELECT @Date = DATEADD(M, 1, DATEADD(D, -DAY(@Date), @Date))
 
     RETURN @Date
 
END

The function does take leap years into consideration, as executing the below select against it, does return “2012-02-29″.

SELECT dbo.fnLastDayOfMonth('2012-02-22')

First Day of the Month

Below is the SQL used to create a function which returns the first day of the month for the date passed in.

CREATE FUNCTION dbo.fnFirstDayOfMonth (@Date DATE)
 
RETURNS DATE
 
AS
 
BEGIN
 
     SELECT @Date = DATEADD(D, -DAY(@Date)+1, @Date)
 
     RETURN @Date
 
END