Calculate the Last Day of the Month using SQL

by Jan 29, 2019

Calculate the Last Day

Today I needed to calculate the last day of the previous month for a Customer.

Ever heard from the function EOMONTH?

Searching on the web I came across a function I never heard from before EOMONTH, this function can be used as of SQL Server 2012.

With this function you never have to worry anymore about how many days(28,29,30,31) your month has.

The function works as follows:

Defined date:

declare @datum datetime = ‘2018.11.01’

SELECT EOMONTH(DATEADD(mm, -1, @datum)) as LastDayPreviousMonth

Getdate()

SELECT EOMONTH(DATEADD(mm, -1, getdate())) as LastDayPreviousMonth

Current Month

SELECT EOMONTH(getdate()) as LastDayCurrentMonth

 

 

 

Feel free to leave a comment

2 Comments

  1. Steven tahon

    The dateadd adds/subtracts on the month number. So between October and November, you still have the issue of 28-29-30-31. I would use EOMONTH(DATEADD(mm, -1, @datum)). You first get to the correct month and then get the last day.

    BR, Steven.

    Reply
    • admin

      Thanks Steven, I have changed it

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

2 × five =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enable Pattern Rules in Azure Purview

How can I enable Pattern Rules?​Pattern Rules Last night I was preparing for a demo with Azure Purview. As always, I walk through all the activity hubs to see if there are any new options. This time I noticed that the Pattern Rules option was greyed out. Resource Set...

Proud to be an ADF Hackathon winner!

Azure Data Factory HackathonAround the clock During the event 'Around the clock Azure SQL and Azure Data Factory' event on Feb 3, 2021, they kicked-off the Azure Data Factory (ADF) Hackathon. Recording of this event can be found here.Winner I submitted a ADF Pipeline...

Are you using Azure DevOps?

Azure DevOpsMore and more users are starting with Azure DevOps. Azure DevOps can be used for up to 5 users free of charge and is therefore a great start to start a project.    Build and Release I will not describe what Azure DevOps can do, because that is too much to...

How to use concurrency in Azure Synapse pipelines?

How to prevent concurrent pipeline execution?Concurrency This week I had a discussion with a colleague about how we can now make sure that a Pipeline does not start when it's already started. He then indicated, have you ever thought of the concurrency option?  I've...

Yeah My website is Finally Live

Yeah, my first built website is LIVEThe last couple of weeks I've been working hard to design my website and finally the moment is there. My website live and I'm really proud.  Building my own website The last couple of weeks/months I've been working hard... to...

Data Factory Pricing

Data Factory pricingAre you also having problems to understand the Pricing Model for Azure Data Factory? After some research on the internet I came across an article which I wanted to share with you. ADFV2 Pricing ExamplesFeel free to leave a comment

Goodbye 2020 Hello 2021

Goodbye 2020 Started to work for InSpark Last year was certainly an eventful year. Started with a new job at InSpark and after 10 weeks we all know what happened, the first intelligent lockdown. The Netherlands was partially locked, but our office was immediately...

Azure Purview MSIgnite Spring 2021 Announcements

Azure Purview Ignite 2021 AnnoucementPricing This week the Azure Purview announced that they will extend the Azure Purview offer to provision 4 Capacity Units of the Data Map for free till May 31, 2021! Charging will start on June 1, 2021. Great news for customer who...

My First Blog Post SQLSatHolland 790

Yeah, my first blog is LIVEAfter a good talk with Reza Rad from RADACAD during SQLSatHolland, I decided to start my first blog!Knowledge sharing is very important, it gives me a lot of energy. But it also gives others people in the community energies to pick up new...

Microsoft Build 2022 Book of News

Microsoft Build 2022MSBuild 2022 has just started. A lot of new Announcement will be released or demonstrated the upcoming days. Like every year or event most of these announcement are added to the Book of News.The book of news can be downloaded from the link below...