How to Get Last Day of Previous Month in SQL

HOW TO GET LAST DAY OF PREVIOUS MONTH IN SQL
Source: Datacatchup

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

to get the last day of previous month in SQL
Source: Datacatchup

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

How can I get the last day of the previous month from a date entered?
Source: Datacatchup

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

To Get Last Day 0f Previous Month In SQL Using EOMONTH()
Source: Datacatchup

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

The EOMONTH() function still returns the correct date result for a leap year. to get the last day of previous month in SQL
Source: Datacatchup

SELECT

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

Syntax

SQL SELECT syntax
Source: Datacatchup

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:

SQL SELECT All syntax
Source: Datacatchup

DECLARE

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

SQL DECLARE syntax
Source: Datacatchup

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

SQL DECLARE variable and data type
Source: Datacatchup

GETDATE()

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

Syntax

SQL GETDATE() syntax
Source: Datacatchup

DATEADD()

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

Syntaxes

SQL DATEADD() SYNTAX to get the last day of previous month in SQL
Source: Datacatchup

DAY()

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

Syntax

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

– (minus sign)

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

Subscribe to get empowered on SQL beginner, Intermediate and advanced topics.

Please follow and like us:

Leave a Reply

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