ERWIN & BUSINESS ANALYTICS

Calculate the Last Day of the Month using SQL

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 *

thirteen + 1 =

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

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 *

five × 5 =

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

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

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

How to check your SQL Server Quota in Azure?

Azure Subscription Usages for SQL Server  Last week we reached our Logical server Quota in Azure. By default you're only allowed to add 20 Logical Servers, but we wanted to have some more for testing purposes.Microsoft Support You can submit a support ticket trough...

Azure Purview Public Preview Starts billing

Billing for Azure Purview(Public Preview)As of January 20th 2021 0:00 UTC Azure Purview will starts billing.Preview From January 20 ,2021 Azure Purview will start billing. During the Public Preview, you will only be billed if you exceed the 4 capacity units for Azure...

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

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

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

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

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

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