SQL filter for last months data

HKFuey
HKFuey used Ask the Experts™
on
I have a SQL view with the criteria below, I'm trying to get last months transactions. My criteria works most of the time except in month 1 of the year.

WHERE        (DATEPART(yyyy, dbo.SLPostedCustomerTran.TransactionDate) = DATEPART(yyyy, DATEADD(m, - 1, GETDATE()))) AND (DATEPART(m, dbo.SLPostedCustomerTran.TransactionDate) = DATEPART(m, DATEADD(m, - 1, GETDATE())))

Can anyone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
it looks ok for me.

select DATEPART(yyyy, DATEADD(m, - 1, '1 Jan 2020')),  DATEPART(m, DATEADD(m, - 1, '1 Jan 2020'))
select DATEPART(yyyy, DATEADD(m, - 1, '1 Feb 2020')),  DATEPART(m, DATEADD(m, - 1, '1 Feb 2020'))
select DATEPART(yyyy, DATEADD(m, - 1, getdate())),  DATEPART(m, DATEADD(m, - 1, getdate()))

Open in new window


can you provide sample data that "got the issues"?
Commented:
2nd answer on this link is what I needed: https://stackoverflow.com/questions/21093502/select-all-where-date-in-last-month-sql

SELECT * FROM Table
WHERE  MONTH(Date) =  DATEPART(MONTH, DATEADD(MONTH, -1, [Date]))
AND YEAR(Date) =    DATEPART(YEAR, DATEADD(MONTH, -1, [Date]))

Author

Commented:
Thanks for quick response!
Ryan ChongSoftware Team Lead

Commented:
you could also try like this:

SELECT * FROM Table
WHERE  MONTH(Date) =  MONTH( DATEADD(MONTH, -1, [Date]))
AND YEAR(Date) =    YEAR( DATEADD(MONTH, -1, [Date]))
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
No.  Never use a function against a table column if you can avoid it.  This is the proper way to do the check you want to do:

WHERE        dbo.SLPostedCustomerTran.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND dbo.SLPostedCustomerTran.TransactionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial