How to get the last day of any month

Example: You are on a project and you need to automate how you retrieve the last day of the month every time you pull data because you use it almost all the time.

This is a quick solution, by using a Scalar-valued function.
Run this:

USE YourDBName
GO
Create Function [dbo].[FnGetLastDayOfMonth]
-- Input parameters
(
@Anydate datetime
)
RETURNS datetime AS
/********************************************************************
Returns the last day of the month (extracted from the date passed)
*********************************************************************/
BEGIN

-- add one month to the datepassed
SET @Anydate = DATEADD(m,1,@Anydate)

RETURN DATEADD(d,-datepart(d,@Anydate),@Anydate)
END


You can test this in a simple select statement;


 SELECT [dbo].[fnGetLastDayOfMonth](GETDATE()) as  LastDayOfTheMonth


Here is the result when i ran it;


Comments

Popular posts from this blog

Azure SQL, Cloud Migration and Modernization

Python - GUI - Tkinter(Bar & Pie Chart)

Bringing Kubernetes to Windows Server apps(Google Cloud Platform)