Get MAX DATE of Previous Month

Question

Hi:

Need help with getting max date of previous month. I think below SQL, I want the output to be 02/27/2023. If I use the EOMMONTH then it gives me the last day of previous month end.

DECLARE @tblData TABLE
(LNID INT,
 AC_CD VARCHAR(100),
 AC_DT DATETIME)

INSERT INTO @tblData VALUES (101,'CPRCLOSE','01/31/2023')
INSERT INTO @tblData VALUES (100,'CPRCLOSE','02/27/2023')
INSERT INTO @tblData VALUES (120,'CPRCLOSE2021','02/26/2023')
INSERT INTO @tblData VALUES (121,'CPRCLOSE2022','03/01/2023')
INSERT INTO @tblData VALUES (200,'CPRCLOSE','03/02/2023')

SELECT     MAX(AC_DT)
FROM      @tblData
WHERE     AC_CD LIKE 'CPRCLOSE%'
AND AC_DT = EOMONTH(DATEADD(month, -1, GETDATE()))

Answer 1

SELECT MAX(AC_DT)

FROM @tblData

WHERE AC_CD LIKE 'CPRCLOSE%'

AND AC_DT > EOMONTH(DATEADD(month, -2, GETDATE()))

and AC_DT <= EOMONTH(DATEADD(month, -1, GETDATE()))

Answer 2

Hi @SQL

;with CTE as(
  select *,datepart(yy,AC_DT) as acyear,datepart(mm,AC_DT) as acmonth from @tblData)
select max(AC_DT) from CTE 
where datepart(yy,dateadd(month,-1,getdate())) = acyear and datepart(mm,dateadd(month,-1,getdate())) = acmonth
group by acyear,acmonth;

Answer 3

How about this

SELECT MAX(AC_DT)

FROM @tblData

WHERE AC_CD LIKE 'CPRCLOSE%'

AND datepart(month, AC_DT) = datepart(month, EOMONTH(DATEADD(month, -1, GETDATE())))

AND datepart(year, AC_DT) = datepart(year, EOMONTH(DATEADD(month, -1, GETDATE())))

So you were expecting the AC_DC to match the last day of the month. You needed to compare Month to Month and year to year.