18 lines
480 B
Transact-SQL
18 lines
480 B
Transact-SQL
USE DevlDUTASJobSchedule;
|
|
GO
|
|
-- Returns the most recent valid workday before or equal to the given date
|
|
CREATE OR ALTER FUNCTION dbo.fn_GetPreviousWorkday (@InputDate DATE)
|
|
RETURNS DATE
|
|
AS
|
|
BEGIN
|
|
DECLARE @Workday DATE = @InputDate;
|
|
|
|
WHILE DATENAME(WEEKDAY, @Workday) IN ('Saturday','Sunday')
|
|
OR EXISTS (SELECT 1 FROM dbo.FederalHolidays WHERE HolidayDate = @Workday)
|
|
BEGIN
|
|
SET @Workday = DATEADD(DAY, -1, @Workday);
|
|
END
|
|
|
|
RETURN @Workday;
|
|
END;
|