SQL for Retreiving the last business day

[starbase59]

I am looking for sql syntax that will pull the last business day(Monday thru Friday), with GETDATE.

For example, using GETDATE:If GETDATE was a Monday, I would want it to retreive Friday, or if GETDATE was a Tuesday, and Monday was a holiday, then Id want to retreive the last business day, which would be Friday.

Has anybody already done this? Is there a function for this?

Many thanks ahead of time.

 

[SQLSister]

Nope you'll have to create your own. After all how would SQL Server know what your business dayas are or whcih specific holidays you take.

Make it a user-defined function, then you can easily use it multiple places.

Suggest you create a table to store the business days for the year and update it onthe first of the year each year.

Alternatively, you can create a holidays table and use the DAtePArt function to determine the day of the week. Then the function would check first to see if the day of the week was one of the predefined normal workdays and then check it against the holidays to make sure that they are not counted. Hope this gives you a starting place.

 

[mwolf00]

Here's a starter....

CREATE FUNCTION dbo.yesterday(@today smallDateTime)
RETURNS smallDateTime
AS
BEGIN
DECLARE @dayNum smallDateTime

-- sunday = 1, sat = 7

SELECT @dayNum = CASE datePart(dw, @today)
WHEN 1 THEN @today - 2
WHEN 2 THEN @today - 3
ELSE @today - 1
END
RETURN @dayNum
END
GO



SELECT BRLive.dbo.yesterday(getDate())

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)