SQL Query Subtract 1 month

sql dateadd
Jayson picture Jayson · Jun 20, 2016 · Viewed 13k times · Source

I need to query SQL for data that falls into a last month date. I thought I was using the correct query logic but I get no results and I know there are results. The snippet of code is this:

MONTH(n.JOIN_DATE) = DATEADD(month, - 1, GETDATE())

This is giving me no results and I need to get anyone who has a join date of last month. What am I missing?

Answer

Tim Biegeleisen picture Tim Biegeleisen · Jun 20, 2016

Use this:

MONTH(n.JOIN_DATE) = MONTH(DATEADD(month, - 1, GETDATE()))

You need to compare apples with apples, so compare the numerical month on both sides of the equation.

Massive credit to @PaulL for figuring this out before I did.

Update:

As @DasBlinkenLight and Matt pointed out, just comparing by month leaves the door open for multiple years to be returned. One possible fix would be to also compare the years, e.g.

WHERE MONTH(n.JOIN_DATE) = MONTH(DATEADD(month, - 1, GETDATE())) AND
      YEAR(n.JOIN_DATE) = YEAR(DATEADD(month, - 1, GETDATE()))

Related questions

MySQL - DATE_ADD month interval

I face a problem with the function DATE_ADD in MySQL. My request looks like this : SELECT * FROM mydb WHERE creationdate BETWEEN "2011-01-01" AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH) GROUP BY MONTH(creationdate) The problem is that, in …

Read More
How to add minutes to the time part of datetime

How to add minutes(INT) to the time part of datetime ? For example : If i have datetime variable like this : @shift_start_time = 2015-11-01 08:00:00.000 @increase = 30 How to get the result : 2015-11-01 08:30:00.000

Read More
How to subtract one month from a Date Column

I know about Dateadd and datediff, but I cannot find any information how to use these functions on an actual date column rather than something like today's date with SQL Server. Say I have the following Column Dated 06/30/2015 07/31/2015 Now I …

Read More