Get MAX DATE of Previous Month

SQL 321 Reputation points
2023-03-07T01:57:32.57+00:00

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


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,896 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jingyang Li 5,891 Reputation points
    2023-03-07T02:03:21.3833333+00:00

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

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-03-07T02:12:10.5166667+00:00

    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;
    
    0 comments No comments

  2. Ron T 11 Reputation points
    2023-03-07T02:25:35.1033333+00:00

    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.

    0 comments No comments