Last Business Day of Each Month

  • Hello All,

    How is your day going? I hope that all is well.

    Does anyone have SQL code to determine the last business day of each month. I do not want to hard code each date. I have the code to pull the last day of each month, but not the last business day. Can anyone provide this for me.

    Thanks

    Andrew SQLDBA

  • Depends on your business. Is Saturday a business day ? What if a public holiday falls on a business day right before a weekend at the end of the month ?

     

  • Do you have a feeling of de ja vu PW?


    Kindest Regards,

  • Just in general, I can take care of the ones that fall odd. Saturday is not a normal business day. Monday - friday is normal business days of teh week.

    Thanks

    Andrew SQLDBA

  • Since you already know the last day of the month, I guess you could write a proc passing in that date and in a loop test it for being a weekday and if not subtract 1 day and loop.  When you hit a weekday then return that date.

  • You can try this UDF - it should do what you need:

    Create Function dbo.ufn_LastBusinessDayOfMonth(

    @dt datetime

    )

    RETURNS datetime

    AS

    BEGIN

    Declare @dt2 datetime

    Declare @Df int

    Declare @dSat int

    Declare @dSun int

    Select @dt2 = DATEADD(d, -1, DATEADD(m, 1 + DATEDIFF(m, 0, @dt), 0))

    -- Since we cannot use SET DATEFIRST within a stored procedure, we must improvise

    -- Default is 7 (Sunday). We want the equivalent of 6 (Saturday)

    Select @dSat = datepart(dw, '2000-01-01') -- Known Saturday

    Select @dSun = (@dSat % 7) + 1

    Select @dt2 = (

    CASE WHEN Datepart(dw, @dt2) = @dSun Then DateAdd(day, -2, @dt2)

    WHEN Datepart(dw, @dt2) = @dSat Then DateAdd(day, -1, @dt2)

    ELSE

    @dt2

    END)

    Return @dt2

    END

  • This could be converted to a UDF to replace GETDATE() with a date for the month to find the last business day of...

    The time included in the date takes you to 23:59:59.997 which is the closest you can get without SQL server rounding up to the next day.  It's handy to have the time in this calc so you can compare with <=.

    --===== Find last business day of current month (not including holidays)

     SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))

          - CASE DATENAME(dw,DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))

                 WHEN 'SUNDAY' THEN 2

                 WHEN 'SATURDAY' THEN 1

                 ELSE 0

             END AS LastBusinessCurrentMonth

    However, depending on what you want to do, you may want to strip the time element off (remove the outer DATEADD) and add 1 to you can compare with < for guaranteed accuracy.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi there....recently had our MI guys ask me for such functions too.

    As we needed to know first of month, last of month, last working day etc, have a number of functions. 2 below will do what you need, and a little more

    FirstLast takes 3 args: any valid date, offset in days (0=current,1=next,-1 last etc), and type 'F' for first of, 'L' for last of.  So to get last day 3 months previous getdate(),-3,'L' etc

    CREATE Function dbo.fn_FirstLast (

     @TheDate datetime,

     @Offset int,

     @type char(1)

    &nbsp

    RETURNS smalldatetime

    AS

    begin

    declare @workingdate smalldatetime

    select @workingdate = 

     cast('01/' +

     CAST(MONTH(DATEADD(MONTH,-@offset,@thedate))as  char(2)) + '/'+

     CAST(YEAR(DATEADD(MONTH,-@offset,@thedate))as  char(4)) as smalldatetime)

    if UPPER(@type) ='L'

      SET @workingdate= dateadd(mm,1,@workingdate)-1

    RETURN @workingdate

    END

    Second function then just takes date and type, so for your purposes, you just need to call this one (it uses the other one)

    call as dbo.fn_FirstLastWorkingDay with the date and 'F' and 'L' for first and last

    Create  function fn_FirstLastWorkingDay (@thedate smalldatetime, @type char(1))

    returns smalldatetime

    as

    begin

    declare @localdate smalldatetime

    set @localdate=dbo.fn_FirstLast(@thedate,0,@type)

    if datename(dw,@localdate) in ('Saturday','Sunday')

     set @localdate=dbo.fn_FirstLastWorkingDay(@localdate,@type)

    return @localdate

    end

    Long answer to your question, but might give you a little more flexibility

    ross

  • Here's a bunch of ways to find different start- endings regarding weeks, months and such..

    -- The example shows how to find out on which dates a week

    -- starts and ends, given an arbitrary date.

    -- Start of weeks are on Mondays, and ends on Fridays

    set datefirst 1 -- Make sure week starts on a monday

    declare @today datetime

    set @today = '2004-03-23'

    select @today as 'present time',

     dateadd(day, 1 - datepart(weekday, @today), @today) as 'first_weekday',

     dateadd(day, 5 - datepart(weekday, @today), @today) as 'last_weekday' -- friday

    -- 2003-03-11 / Kenneth Wilhelmsson

    -- This is a sample matrix showing how to find out dates of month boundries

    -- from any given point in time.

    set nocount on

    declare @date datetime

    set     @date = getdate() -- the point in time from which to measure

    print '''Today''s date'' is: ' + convert(char(10), @date, 121)

    print ' '

    -- date of the 1st of the current month

    select convert(char(6), @date, 112) + '01' as '1st this month'

    -- date of the last day of current month

    select dateadd(day, -1, dateadd(month, 1, convert(char(6), @date, 112) + '01')) as 'last this month'

    -- date of the 1st of the previous month

    select dateadd(month, -1, convert(char(6), @date, 112) + '01') as '1st of last month'

    -- date of the last day of the previous month

    select dateadd(day, -1, convert(char(6), @date, 112) + '01') as 'last of last month'

    -- date of the 1st of the next month

    select dateadd(month, 1, convert(char(6), @date, 112) + '01') as '1st of next month'

    -- date of the last day of the next month

    select dateadd(day, -1, dateadd(month, 2, convert(char(6), @date, 112) + '01')) as 'last of next month'

    set nocount off

    -- alternative way of finding first day in the month of a given in-date

    declare @date datetime

    set     @date = '1898-06-04 12:42:28.653' -- the point in time from which to measure

    select DATEADD(mm, DATEDIFF(mm,0,@date), 0)

    /Kenneth

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply