Subtracting one month from a date but making sure it is the first business day of that month

Question

I need to subtract one month from a date called ArchiveDate but I want the first business day of that month. For example if my ArchiveDate is 9/2/2018 I would like to have 8/1/2019.

This is what I have:

DECLARE @ArchiveDate date = '9/2/2019'

SELECT ArchiveDate = DATEADD(day, 
        CASE WHEN 
            DATEPART(weekday, DATEADD(MONTH, -1, @ArchiveDate)) = 1
            THEN 1
        WHEN DATEPART(weekday, DATEADD(MONTH, -1, @ArchiveDate)) = 7 THEN 2
        ELSE 0
        END
    , DATEADD(MONTH, -1, @ArchiveDate))

What I get from this is 8/2/2019 but as you can see I want 8/1/2019.

Answer 1

SELECT 
    CASE 
        WHEN DATENAME(WEEKDAY, DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0)) = 'Saturday'
            THEN DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0) + 2
        WHEN DATENAME(WEEKDAY, DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0)) = 'Sunday'
            THEN DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0) + 1
        ELSE 
            DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0)
    END

This will return the first BUSINESS DAY of the previous month.

Answer 2

you can use eomonth along with your logic to get first day of previous month as below:

DECLARE @ArchiveDate date = '9/2/2019'

select dateadd(day, 1, eomonth(dateadd(month, -2, @ArchiveDate)));