CODEWITHSUNDEEP
sqldateadd
Jayson
Jayson

Reputation: 83

SQL Query Subtract 1 month

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?

Upvotes: 2

Views: 27130

Answers (7)

Jayson
Jayson

Reputation: 83

I knew this was a simple one and I was missing something to it. My code was wrong based upon the many responses I received on this and I was comparing apples to oranges and not apples to apples. Once I added the Month() around the dateadd function, it worked.

Upvotes: -1

Matt
Matt

Reputation: 14341

SELECT
    DATEFROMPARTS(YEAR(DATEADD(month,-1,GETDATE())),MONTH(DATEADD(month,-1,GETDATE())),1) AS StartOfLastMonth
    ,DATEADD(day,-1,(DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1))) AS EndOfLastMonthAsDate
    ,DATEADD(day,-3,CAST(DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1) AS DATETIME)) AS EndOfLastMonthMidngith
    ,CAST(DATEADD(month,-1,GETDATE()) AS DATE) AS OneMonthAgoStrartOfDay
    ,CAST(GETDATE() AS DATE) AS StartOfToday
    ,DATEADD(MS,-3,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AS MidnightLastNight

Okay As people have definitely illustrated there are a lot of different answers to your question and all are built upon similar premise. using DATEADD() with a negative number to go back a month. Or to compare month and year to see if they are the same. The former being geared at 1 month ago to today and the later being last month.

All of the answers so far, expect @DasBlinkenLight and @TimBiegeleisen, fail to take into account TIME component of your column and the GETDATE() function if you have one. If your column is DATETIME you will need to take that into account. The above SELECT query that will arm you with some ways of getting to different dates that i suspect will meet your needs.

As far as using BETWEEN with dates be careful! because the values you put in are inclusive so if you put GETDATE() on the right side of the between statement you will get today's results too but you might really want UP TO to today in which case you should change your right side argument. Also I am not sure about Oracle, mysql, etc. but Micrsofot SQL-Server is accurate to .003 milliseconds. So if you really want to look at midnight of a date you should look at 23:59:59.997 because .998 and .999 will round up to the next day.

Also to further simplify if you don't want time components you can also cast your column to DATE and it essentially drops off the time and the BETWEEN because a little clearer too, e.g. CAST(n.JOIN_DATE AS DATE)

There are definitely other questions on this subject on stackoverflow I encourage you to research.

Upvotes: 0

hieubv.cntt
hieubv.cntt

Reputation: 96

MONTH(n.JOIN_DATE) returns a numeric which indicate the month in date m.JOIN_DATE
DATEADD(month, - 1, GETDATE()) returns a date which indicate date in last month.
So, you can use this instead :
MONTH(n.JOIN_DATE)= MONTH(DATEADD(month, - 1, GETDATE()))
OR
n.JOIN_DATE = DATEADD(month, - 1, GETDATE())

Upvotes: 2

damola
damola

Reputation: 292

The reply to this answer are correct. But in the light of best practice writing your query this way will make it less SARGABLE, hence making it ignore indexes if you have one. It might be better to write it as 

WHERE n.JOIN_DATE between DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)  AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

based on the comment below I have modified the query. I guess I did not read the question in depth.

Upvotes: -2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

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()))

Upvotes: 6

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726699

MONTH(...) produces a month number. You should not compare it to the result returned by DATEADD, which is actually a date.

If you are looking for everyone who has joined less than a month ago, you can do it like this:

WHERE DATEADD(month, 1, n.JOIN_DATE) > GETDATE()

This takes into account the year and the day as well, not only the month.

If you are looking for everyone who joined last month, no matter on what day, you can use a more complex condition:

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

The second condition is necessary to avoid confusion between members joining last month and members joining on the same month one or more years ago.

Upvotes: 2

Siyual
Siyual

Reputation: 16917

MONTH(n.JOIN_DATE) will only return the numerical value of the month (e.g.: 11 or 5).

DATEADD(MONTH, -1, GETDATE()) will simply subtract one month from the current date. It is still in a DATETIME format.

You may be looking for: 

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

Upvotes: 1

Related Questions