Tuesday, July 24, 2007

A UDF To Get Previous Working Day in SQL Server 2005

It has pretty similar logic as we done with the UDF of Next Working Day. You can read the earlier post on "How to get the Week Day" for better understanding. Here is the self explanatory code.

CREATE FUNCTION dbo.udf_GetPreviousWorkingDay (@p_Date DATETIME )
RETURNS DATETIME
AS
BEGIN
DECLARE @m_WeekDay INT
DECLARE @rt_Prev_Working_Day DATETIME
SET @m_WeekDay = DATEPART(weekday,@p_Date)

IF @m_WeekDay = 1 -- IF SUNDAY then subtract 2 fromsunday to get friday that is working day
SET @rt_Prev_Working_Day = DATEADD(d,-2,@p_Date)
ELSE IF @m_WeekDay = 2 -- IF MONDAY then subtract 3 fromsunday to get friday that is working day
SET @rt_Prev_Working_Day = DATEADD(d,-3,@p_Date)
ELSE -- ELSE subtract one to get the prev date
SET @rt_Prev_Working_Day = DATEADD(d,-1,@p_Date)

RETURN @rt_Prev_Working_Day
END

Here are some of the test cases having the same result:

SELECT dbo.udf_GetPreviousWorkingDay ( '07/21/2007')
SELECT dbo.udf_GetPreviousWorkingDay ( '07/22/2007')
SELECT dbo.udf_GetPreviousWorkingDay ( '07/23/2007')

Result:

2007-07-20 00:00:00.000

No comments: