SQL – Get Last Date of the Month

SQL Server 2012 has introduced an EOMONTH function to get the last month of the any date which was very tricky before that.
Today, we are going to use this function to get the last date of the current month, previous month, next month and the same for the next year as well as for previous year.

Note: EOMONTH function can be worked to SQL Server 2012 servers and higher and will not work for versions lower than SQL Server 2012.

EOMONTH function takes two parameters where the second parameter is optional. The simple syntax for using EOMONTH function is given below: 

EOMONTH ( Start Date [, month to add] ) Parameters start_date: Date expression specifying the date for which to return the last day of the month. month_to_add: Optional integer expression specifying the number of months to add to start_date. If month_to_add is specified, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then an error is raised. Note: This function always return date as data type.

Last Date of the Month

With the help of this function, we will get the last date of the any month and will not paas the second parameter in that function as given below:

---- Declare local variable and set the date DECLARE @inpDate DATETIME='2015-10-22' PRINT 'Current Date: '+ CONVERT(VARCHAR,CAST(@inpDate AS DATE)) ----- EOD For Current Month SET @inpDate=EOMONTH(@inpDate) PRINT 'EOD For Current Month: '+ CONVERT(VARCHAR,CAST(@inpDate as Date)) Output Current Date: 2015-10-22 EOD For Current Month: 2015-10-31

Last Date of the Next Month

To get the last date of the next month, we need to pass the value in the second parameter. For this, we will use 1 as the second parameter to tell the function that we need to add 1 month in the first date parameter as shown below:

---- EOD for Next month ---- Declare local variable and set the date DECLARE @inpDate DATETIME='2015-10-22' ---- Need to add 1 as the second parameter SET @inpDate=EOMONTH(@inpDate,1) PRINT 'EOD for Next Month: '+ CONVERT(VARCHAR,CAST(@inpDate as Date)) Output EOD for Next Month: 2015-11-30

Last Date of the Previous Month

To get the last date of the previous month, we need to pass the value in the second parameter. For this, we will use -1 as the second parameter to tell the function that we need to subtract 1 month in the first date parameter as shown below:

----- EOD for Previous Month ---- Declare local variable and set the date DECLARE @inpDate DATETIME='2015-10-22' ---- Need to add -1 as the second parameter SET @inpDate=EOMONTH(@inpDate,-1) PRINT 'EOD for Previous Month: '+ CONVERT(VARCHAR,CAST(@inpDate as Date)) Output EOD for Previous Month: 2015-09-30

Last Date of the Previous Year

To get the last date of the previous year for the same month, we will use -12 as the second parameter to tell the function that we need to subtract -12 month in the first date parameter because a year always have 12 month as shown below: 

----- EOD for Last Year  ---- Declare local variable and set the date DECLARE @inpDate DATETIME='2015-10-22' ---- Need to add -12 as the second parameter SET @inpDate=EOMONTH(@inpDate,-12) PRINT 'EOD for Last Year: '+ CONVERT(VARCHAR,CAST(@inpDate as Date)) Output EOD for Last Year: 2014-10-31

Last Date of the Next Year

To get the last date of the next year for the same month, we will use 12 as the second parameter to tell the function that we need to add 12 month in the first date parameter because a year always have 12 month as shown below:

----- EOD for Next Year ---- Declare local variable and set the date DECLARE @inpDate DATETIME='2015-10-22' ---- Need to add 12 as the second parameter SET @inpDate=EOMONTH(@inpDate,-1) PRINT 'EOD for Next Year: '+ CONVERT(VARCHAR,CAST(@inpDate as Date)) Output EOD for Next Year: 2016-10-31

By using this function, there is no need to create any function and apply more logics to get the last date of the any month for any year. EOD for all the above scenarios in a glance with select statement as given below:

---- Declare local variable and set the date DECLARE @inpDate DATETIME='2015-10-22' SELECT --- Date Value in the parameter [Current Date]= CONVERT(VARCHAR,Cast(@inpDate as date)), --- EOD For Current Month [EOD For Current Month]=CONVERT(VARCHAR,EOMONTH(@inpDate)), --- EOD for Next Month [EOD for Next Month]= CONVERT(VARCHAR,EOMONTH(@inpDate,1)), --- EOD for Previous Month [EOD for Previous Month]= CONVERT(VARCHAR,EOMONTH(@inpDate,-1)), --- EOD for Last Year [EOD for Last Year]= CONVERT(VARCHAR,EOMONTH(@inpDate,-12)), --- EOD for Next Year [EOD for Next Year]= CONVERT(VARCHAR,EOMONTH(@inpDate,12)) Current Date EOD For Current Month EOD for Next Month EOD for Previous Month EOD for Last Year EOD for Next Year 10/22/2015 10/31/2015 11/30/2015 09/30/2015 10/31/2014 10/31/2016

Learn more on another features of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL - Stored Procedure Vs Ad-Hoc (In-Line)
8. SQL - Stored Procedure Vs Functions
9. SQL - Merge Statement
10. SQL - Functions
11. SQL - Cursors