How To Get Last Day Of Previous Month In SQL

How to get last day of previous month in SQL

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:

Query 1

DECLARE @date DATETIME = GETDATE()

SELECT DATEADD(DAY, -(DAY(@date)), @date)

Query 2

SELECT DATEADD(day,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0))

Query 3

SELECT EOMONTH(DATEADD(month, -1, Current_timestamp))

Results for Queries 1,2 3

Select statement results for Query 1-3 above

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)

Result

Select DateAdd

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]

Result

EOMONTH()

NOTE:

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;

Result

EOMONTH()

SELECT

The SELECT statement is used to select data from a database.

Syntax

Select statement

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:

Select statement

DECLARE

The DECLARE statement initializes a variable by assigning it a name and a data type. The variable name must start with the @ sign.

Declare

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.

Syntax

Declare

GETDATE()

The GETDATE() function returns the current database system date and time, in a ‘YYYY-MM-DD hh:mm:ss.mmm’ format.

Syntax

GetDate()

DATEADD

The DATEADD() function adds a time/date interval to a date and then returns the date.

Syntaxes

DateAdd()

DAY()

The DAY() function returns an integer value known as the day of the month (from 1 to 31) for a specified date.

Syntax

Sql day syntax

– (minus sign)

The minus sign (-) in query above is for subtracting days.