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
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
Post a Comment