Click here to Skip to main content
15,119,897 members
Articles / Programming Languages / SQL
Technical Blog
Posted 30 Aug 2016

Tagged as

Stats

11.8K views
5 bookmarked

How Do You Calculate the End of the Month in SQL?

Rate me:
Please Sign up or sign in to vote.
4.43/5 (8 votes)
30 Aug 2016MIT3 min read
How to calculate the end of the month in SQL

When working with SQL dates, sometimes you need to calculate the end of the month. Months are tricky! Some are 28 days, others 30 or 31, and now and then there’s a leap year!

So, given a date, how do you calculate the number of days remaining in the month?

The calculation is really a two-step process:

  1. Determine the last date of the month
  2. Calculate the difference in days, between the date in question and the step 1

We can use the DATEDIFF function to calculate the difference in days, but how do you calculate the last day of the month?

Using EOMONTH to Calculate the End of the Month

In SQL Server 2012 and above, you can use the EOMONTH function to return the last day of the month.

For example:

SQL
SELECT EOMONTH('02/04/2016')

returns 02/29/2016.

As you can see, the EOMONTH function takes into account leap year.

So to calculate the number of days from a date to the end of the month, you could write:

SQL
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))

which returns 25.

Let’s try a more comprehensive example that calculates the days remaining on a LoanDate for the LoanDate’s current month:

SQL
BEGIN TRANSACTION
-- Sample Data
CREATE TABLE LoanDate (LoanID INT, LoanDate DATE);
INSERT INTO LoanDate Values (1, '1/1/2016');
INSERT INTO LoanDate Values (1, '1/15/2016');
INSERT INTO LoanDate Values (1, '1/31/2016');
INSERT INTO LoanDate Values (1, '2/15/2016');
INSERT INTO LoanDate Values (1, '3/15/2016');
INSERT INTO LoanDate Values (1, '4/15/2016');
INSERT INTO LoanDate Values (1, '5/15/2016');
INSERT INTO LoanDate Values (1, '6/15/2016');
INSERT INTO LoanDate Values (1, '7/15/2016');
INSERT INTO LoanDate Values (1, '8/15/2016');
INSERT INTO LoanDate Values (1, '9/15/2016');
INSERT INTO LoanDate Values (1, '10/15/2016');
INSERT INTO LoanDate Values (1, '11/15/2016');
INSERT INTO LoanDate Values (1, '12/15/2016');

-- Select LoanDate, Days in Month, and Days Remaining in Month
SELECT LoanID,
       LoanDate,
       EOMONTH(LoanDate) LoanDateEndOfMonth,
       DATEDIFF(d, LoanDate, EOMONTH(LoanDate)) as DaysRemaining
FROM   LoanDate
ROLLBACK

Here is the result:

End of Month Calculation using EOMONTH

As you can see, the DaysRemaining changes according to the month. Also, notice that when the date occurs on the same date as the end of the month, such as row 3, zero days remain.

Now let’s look at how you would go about calculating this if you are using SQL 2008 R2 or before.

Old School Method to Calculate Last Day of Month

The calculation is really the same two-step process:

  1. Determine the last date of the month
  2. Calculate the difference in days, between the date in question and the step 1

However, the difference is in how we determine the last date of the month. Since EOMONTH isn’t available, we need to calculate it the “old fashioned” way. There are several methods to do this. Here is one.

We’ll calculate the last day of the month using two functions: DATEADD and DAY.

We’ll use DATEADD to add a month to the date. Then the DAY function to determine the number of days from the beginning of the month. By subtracting this from the date we just calculated (the one that is a month ahead), we can get the last date of the month.

OK, I know that sounds confusing, so let’s break it down using this diagram:

Visual Explanation of How to Calculate the End of the Month

Using the same example as we did with EOMONTH, we have the following statement we can use to test:

SQL
BEGIN TRANSACTION
-- Sample Data
CREATE TABLE LoanDate (LoanID INT, LoanDate DATE);
INSERT INTO LoanDate Values (1, '1/1/2016');
INSERT INTO LoanDate Values (1, '1/15/2016');
INSERT INTO LoanDate Values (1, '1/31/2016');
INSERT INTO LoanDate Values (1, '2/15/2016');
INSERT INTO LoanDate Values (1, '3/15/2016');
INSERT INTO LoanDate Values (1, '4/15/2016');
INSERT INTO LoanDate Values (1, '5/15/2016');
INSERT INTO LoanDate Values (1, '6/15/2016');
INSERT INTO LoanDate Values (1, '7/15/2016');
INSERT INTO LoanDate Values (1, '8/15/2016');
INSERT INTO LoanDate Values (1, '9/15/2016');
INSERT INTO LoanDate Values (1, '10/15/2016');
INSERT INTO LoanDate Values (1, '11/15/2016');
INSERT INTO LoanDate Values (1, '12/15/2016');

-- Select LoanDate, Days in Month, and Days Remaining in Month
SELECT LoanID,
       LoanDate,
       DATEADD(dd,-(DAY(DATEADD(mm,1,LoanDate))), 
               DATEADD(mm,1,LoanDate)) LoanDateEndOfMonth,
       DATEDIFF(d,LoanDate, DATEADD(dd,-(DAY(DATEADD(mm,1,LoanDate))), 
                            DATEADD(mm,1,LoanDate))) as DaysRemaining
FROM   LoanDate
ROLLBACK

Here are the results:

So which method should you use?

If I’m writing code and I know it will run on SQLServer 2012 or greater, I would go for the EOMONTH method as it is much easier to read; however, I think knowing and understanding the method using DATEADD and DAY help you better understand how to manipulate dates.

So what other ways have you seen to calculate the last date in a month? I would love to see them. Just let me know in the comments below.

The post How Do You Calculate the End of the Month in SQL? appeared first on Essential SQL.

This article was originally posted at http://www.essentialsql.com/calculate-end-month-sql

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
Questionlittle correction Pin
Abe.r100031-Aug-16 11:19
MemberAbe.r100031-Aug-16 11:19 
QuestionHere's my solution Pin
SqlNightOwl31-Aug-16 9:41
professionalSqlNightOwl31-Aug-16 9:41 
QuestionEasiest method... Pin
NightWizzard30-Aug-16 7:39
MemberNightWizzard30-Aug-16 7:39 
...is: jump to the first day in month of any given date by replacing the day by 1 (e.G.: 08/30/2016 -> 08/01/2016). Use DateAdd to add 1 month first, then use DateAdd again to substract 1 day and voila: there's the last day in month!
SuggestionSuggestion Pin
SteveHolle30-Aug-16 7:21
MemberSteveHolle30-Aug-16 7:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.