SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
                        WHEN 'Sunday' THEN -2 
                        WHEN 'Monday' THEN -3 
                        ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))

I prefer to use DATENAME for things like this over DATEPART as it removes the need for Setting DATEFIRST And ensures that variations on time/date settings on local machines do not affect the results. Finally DATEDIFF(DAY, 0, GETDATE()) will remove the time part of GETDATE() removing the need to convert to varchar (much slower).

EDIT (almost 2 years on)

This answer was very early in my SO career and it annoys me everytime it gets upvoted because I no longer agree with the sentiment of using DATENAME.

A much more rubust solution would be:

SELECT  DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 
                        WHEN 1 THEN -2 
                        WHEN 2 THEN -3 
                        ELSE -1 
                    END, DATEDIFF(DAY, 0, GETDATE()));

This will work for all language and DATEFIRST settings.

This function returns last working day and takes into account holidays and weekends. You will need to create a simple holiday table.

-- =============================================
-- Author:      Dale Kilian
-- Create date: 2019-04-29
-- Description: recursive function returns last work day for weekends and 
-- holidays
-- =============================================
ALTER FUNCTION dbo.fnGetWorkWeekday
(
    @theDate DATE
)
RETURNS DATE
AS
BEGIN

DECLARE @importDate DATE = @theDate
DECLARE @returnDate DATE
--Holidays
IF EXISTS(SELECT 1 FROM dbo.Holidays WHERE isDeleted = 0 AND @theDate = Holiday_Date)
BEGIN
SET @importDate = DATEADD(DAY,-1,@theDate);
SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Satruday
IF(DATEPART(WEEKDAY,@theDate) = 7)
BEGIN
    SET @importDate = DATEADD(DAY,-1,@theDate);
    SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Sunday
IF(DATEPART(WEEKDAY,@theDate) = 1)
BEGIN
    SET @importDate = DATEADD(DAY,-2,@theDate);
    SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END

RETURN @importDate;
END
GO

Then how about:

declare @dt datetime='1 dec 2012'

select case when 8-@@DATEFIRST=DATEPART(dw,@dt)  
            then DATEADD(d,-2,@dt)  
        when (9-@@DATEFIRST)%7=DATEPART(dw,@dt)%7  
            then DATEADD(d,-3,@dt)  
        else DATEADD(d,-1,@dt)  
    end

The simplest solution to find the previous business day is to use a calendar table with a column called IsBusinessDay or something similar. The your query is something like this:

select max(BaseDate)
from dbo.Calendar c
where c.IsBusinessDay = 0x1 and c.BaseDate < @InputDate

The problem with using functions is that when (not if) you have to create exceptions for any reason (national holidays etc.) the code quickly becomes unmaintainable; with the table, you just UPDATE a single value. A table also makes it much easier to answer questions like "how many business days are there between dates X and Y", which are quite common in reporting tasks.

You can easily make this a function call, adding a second param to replace GetDate() with whatever date you wanted. It will work for any day of the week, at any date range, if you change GetDate(). It will not change the date if the day of week is the input date (GetDate())

Declare @DayOfWeek As Integer = 2   -- Monday

Select DateAdd(Day, ((DatePart(dw,GetDate()) + (7 - @DayOfWeek)) * -1) % 7, Convert(Date,GetDate()))

More elegant:

select DATEADD(DAY, 
CASE when datepart (dw,Getdate()) < 3 then datepart (dw,Getdate()) * -1 + -1 ELSE -1 END,
cast(GETDATE() as date))

select  
  dateadd(dd, 
             case DATEPART(dw, getdate()) 
             when 1 
             then -2 
             when 2 
             then -3 
             else -1 
         end, GETDATE())

thanks for the tips above, I had a slight variant on the query in that my user needed all values for the previous business date. For example, today is a Monday so he needs everything between last Friday at midnight through to Saturday at Midnight. I did this using a combo of the above, and "between", just if anyone is interested. I'm not a massive techie.

-- Declare a variable for the start and end dates.
declare @StartDate as datetime 
declare @EndDate as datetime 

SELECT  @StartDate = DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
WHEN 'Sunday' THEN -2 
WHEN 'Monday' THEN -3 
    ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))
select @EndDate = @StartDate + 1 
select @StartDate , @EndDate 
-- Later on in the query use "between"
and mydate between @StartDate and @EndDate

Dale Kilian's code is slightly buggy, here is the corrected version, where this also considers holiday's stored in another table.

Please ignore my formatting error, I am not good at posting here.

ALTER FUNCTION [dbo].[fnGetPreviousWorkDay]
(
    @inputDate DATE
)
RETURNS DATE
AS
BEGIN

DECLARE @outputDate DATE =  DATEADD(day,-1,@inputDate )
--Holidays
IF EXISTS(SELECT 1 FROM HoliDayList WHERE @outputDate = HoliDayDate)
BEGIN
SET @outputDate = (SELECT dbo.fnGetPreviousWorkDay(@outputDate ))
END
--Satruday
IF(DATEPART(WEEKDAY,@outputDate ) = 7)
BEGIN
    SET @outputDate = (SELECT dbo.fnGetPreviousWorkDay(@outputDate ))
END
--Sunday
IF(DATEPART(WEEKDAY,@outputDate ) = 1)
BEGIN
    SET @outputDate = (SELECT dbo.fnGetPreviousWorkDay(@outputDate ))
END

RETURN @outputDate ;