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
    Steven tahon on February 1, 2019 at 2:09 pm

    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
      admin on February 1, 2019 at 2:18 pm

      Thanks Steven, I have changed it

      Reply

Submit a Comment

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

two − 2 =

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

LEAP 2018

Lead Enterprise Architect ProgramFrom the 4th of December until the 6th of December I've joined the LEAP (Lead Enterprise Architect Program) 2018 Data/AI from Microsoft NL. Three  days on the Microsoft Campus in Redmond joining business and technical sessions from a...

Parameterize Linked Services in ADF

Parameterize Linked Services For my Azure Data Factory solution I wanted to Parameterize properties in my Linked Services. Not all properties are Parameterized by default through the UI. But there's another way to achieve this.    Linked Service Open your existing...

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...

Calculate Workingdays including Holidays with T-SQL

Calculate Workingdays between 2 Date columnsRecently I have been getting some questions from my customers, can I calculate the number of workdays between 2 dates? Of course my answer was, yes you can. But I do want certain closing dates and holidays of our company not...

Restore a accidentally deleted Azure SQL Database

Help I deleted my Logical Server​OOPS Have you ever experienced that you accidentally deleted your Logical Server in Azure?  Because, for example, you made your Pipeline wrong. Surely. And of course you didn't have a backup in your storage either.Well I must confess...

Rerun Pipeline activities in Azure Data Factory

Rerun Pipeline activities in ADF! As of today you can rerun or partially, yes you’re reading it correct partially, rerun you Azure Data Factory pipeline.Where you previously had to run the entire Pipeline again, you can now run a part of the Pipeline. This can save a...

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...

Goodbye 2021, Hello 2022

Goodbye 2021Recap First of all, I would like to wish everyone a very beautiful and healthy 2022. We are now 3 days on the road into the new year and it is always good to look back at what happened last year. It's certainly been an eventful year, topped off with my MVP...

Service Health Alerts in Azure

Maintenance and Service Healths in AzureI get often questions, is there no maintenance in Azure. Like every data center, the Azure data center also needs maintenance. You can even be notified when a specific service, in a specific data center, has scheduled...

High Concurrency Notebook Activity in Microsoft Fabric

Microsoft Fabric is a cloud-based platform that provides integrated data engineering and data science solutions. One of the key features of Microsoft Fabric is the support for Apache Spark, a distributed computing framework that enables large-scale data processing and...