Back to cookbooks list Articles Cookbook

How to Get the Previous Month in SQL

  • EXTRACT()
  • CURRENT_TIMESTAMP
  • INTERVAL

Table of Contents

Problem:

You would like to display the previous month (without time information) in a database.

Solution:

For PostgreSQL and MySQL:

SELECT 
  EXTRACT(MONTH FROM CURRENT_TIMESTAMP - INTERVAL '1' MONTH) AS previous_month;

For Oracle:

SELECT 
  EXTRACT(MONTH FROM CURRENT_TIMESTAMP - INTERVAL '1' MONTH) AS previous_month
FROM dual;

Assuming today is March 4, 2020, the result is:

previous_month
2

Discussion:

To get the previous month, subtract one month from today's date. Use CURRENT_TIMESTAMP to get today's date. Then, subtract 1 month from the current date using INTERVAL '1' MONTH. Finally, extract the month from the date using the EXTRACT() function with the MONTH parameter.

Recommended courses:

Recommended articles:

See also: