0

Here is my query: 

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))
  SELECT
    @dSat = DATEPART(dw, '2018-01-06') -- 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
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
user9530119
  • 11
  • 2
  • 3
    What about holidays? I think the best approach is simple calendar table with `is_working` flag. Then simple get max date per month which has `is_working=1`. – Lukasz Szozda Mar 21 '18 at 17:14
  • I'm thinking of normal working week Monday-Friday (not including holidays). Thank you! – user9530119 Mar 21 '18 at 17:23
  • Procedural code is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Mar 21 '18 at 17:44
  • I apologize, I use SQL-Server. Thank you! – user9530119 Mar 21 '18 at 17:54
  • https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations – Sean Lange Mar 21 '18 at 18:19

1 Answers1

0
DECLARE @Dt datetime
SET @Dt = '03/21/2018'

DECLARE @lastOfMonth datetime, @3DaysBeforeTheEnd DATETIME

set @lastOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Dt)+1,0))
SET @3DaysBeforeTheEnd = DATEADD(d,-2,@lastOfMonth)



select top 1 DATENAME(weekday, x.[Date]), x.[Date]
from (
SELECT  TOP (DATEDIFF(DAY, @3DaysBeforeTheEnd, @lastOfMonth) + 1)
        [Date] = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @3DaysBeforeTheEnd)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b
        )x
WHERE DATENAME(weekday, x.[Date]) NOT IN ('Saturday','sunday')
order by x.[Date] desc
Matthew Weir
  • 186
  • 1
  • 1
  • 9
  • You may also want to see this post: https://stackoverflow.com/questions/24407270/calculate-total-business-working-days-between-two-dates – Matthew Weir Mar 21 '18 at 18:58