SQL has become a powerful tool for data analysis and manipulation of data. The EOMONTH() function returns the last day of the month of a specified date. EOMONTH() can be used as a worksheet function (WS) in Excel.
However, to get the last day of previous month in SQL can be achieved with the following SQL syntax below:
DECLARE @date DATETIME = GETDATE()
SELECT DATEADD(DAY, -(DAY(@date)), @date)
SELECT DATEADD(day,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0))
SELECT EOMONTH(DATEADD(month, -1, Current_timestamp))
Results for Queries 1,2, 3
How can I get the last day of the previous month from a date entered?
DECLARE @dateCurrent DATE = ‘2020-01-31’
SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, @dateCurrent)-1, -1)
To Get Last Day 0f Previous Month In SQL Using EOMONTH()
The EOMONTH() function returns the last day of the month of a specified date .
DECLARE @Date DATE = ‘2020-02-18’
SELECT @Date AS Today,
EOMONTH(@Date) AS [Last Day of this Month]
The EOMONTH() function still returns the correct date result for a leap year.
How to use EOMONTH() function to get the number of days in a specified month
This example returns the number of days of February 2020:
SELECT DAY(EOMONTH(‘2020-02-09’)) days;
The SELECT statement is used to select data from a database.
In the syntax above, column1, column2, … are the field names of the table you want to select data from. To select all the fields available in a table, use the following syntax:
The DECLARE statement initializes a variable by assigning it a name and a data type. The variable name must start with the @ sign.
In the above example, the variable is @date and data type is datetime. By default, when a variable is declared, its value is set to NULL.
A variable is an object that holds a single value of a specific type e.g: integer, date, or varying character string.
The GETDATE() function returns the current database system date and time, in a ‘YYYY-MM-DD hh:mm:ss.mmm’ format.
The DATEADD() function adds a time/date interval to a date and then returns the date.
The DAY() function returns an integer value known as the day of the month (from 1 to 31) for a specified date.
– (minus sign)
The minus sign(-) in query above is for subtracting days.
Subscribe to get empowered on SQL beginner, Intermediate and advanced topics.