Select last business day function

Posted on 2006-05-30
Last Modified: 2012-06-27
How I would I calculate the last business day, not including today?
For example, The last business day would be Friday 5/26/2006
I have a table called holidays and it has 5/29/2006 in it.
Question by:JRockFL
    LVL 5

    Accepted Solution

    Hi JRockFL,

    The easiest way to handle this is to have an entire calendar table (not just holidays).. A good link with most of the code to create, populate, and query a table like this can be found here:

    With this table, you could then select the max(d) where the month = the current month, or next month, or whatever.

    LVL 28

    Expert Comment

    Assume your last working day is Friday and weekend starts from Saturday then use the below code
    it will give you last working day for the current month

    declare @LastDate as datetime
    select  @LastDate  =
          cast(datepart(year,dateadd(month,1,getdate())) as varchar(10))+'-'+
          cast(datepart(month,dateadd(month,1,getdate())) as varchar(10))+'-'+
          '01' as datetime)
    WHILE  datename(weekday,@LastDate) <> 'Friday'
    set @LastDate = dateadd(day,-1,@LastDate)

    select @LastDate

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now