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

Leave a Reply

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

Azure Synapse Analytics

Azure Synapse Analytics  Insights for all Azure Synapse provides a breathtaking view of your data across data warehouses and big data analytics systems. Bringing these two worlds together into a single service is challenging as it requires unifying similar...

Book of News Microsoft Ignite 2019

Microsoft Ignite 2019MSIgnite 2019 has almost come to an end. A lot of new Annoucement have been released and presented.  Would you also like to know which announcements have been made?Microsoft has released during MSIgnite a "Book of News". This book contains all new...

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

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

Azure Data Factory: Save a pipeline as a Template

Saving a PipelineAnother great improvement in Azure Data Factory. Saving you template!How can you save your template? First of all you need to connect your Azure Data Factory to  a GIT integration. Both Azure DevOps GIT and GitHub are supported. Please follow this...

Azure Synapse Analytics Code Repository has arrived

Azure Synapse Analytics Code repository‎I just opened my Azure Synapse Analytics Workspace and got a great surprise, the option Git Configuration is available as of today‎.    After a long wait, today the Git Configuration option became available in Azure Synapse...

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

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

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

Working from Home: How do I get my energy and focus back on track?

Working from home It has now been exactly 7 weeks since I started working from home. In the beginning this went with full energy, but now gradually the energy and the focus on the work is starting to drain.I'm the only one in here? I'm sure, I'm not. I regularly hear...

Discover more from Erwin | Data & Intelligence

Subscribe to get the latest posts sent to your email.