How show only first friday of every month

Question

Post reply

How show only first friday of every month

Viewing 2 posts - 61 through 61 (of 61 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Jonathan AC Roberts wrote:

Jeff Moden wrote:

There is a serious problem with the "equivalent" code you wrote though. It's not "equivalent" in the results for the last 3 tests. The table code comes up a month short.

Yes, I noticed the difference, I just thought I'd put it down to a feature of the code. It's an arguable point whether you'd want a date returned outside the start-end date range.

Maybe but you weren't consistent. 😉

--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)
Intro to Tally Tables and Functions

Jonathan AC Roberts SSCoach Points: 17601 More actions · Answer 2

Jeff Moden wrote:

Jonathan AC Roberts wrote:

Jeff Moden wrote:

There is a serious problem with the "equivalent" code you wrote though. It's not "equivalent" in the results for the last 3 tests. The table code comes up a month short.

Yes, I noticed the difference, I just thought I'd put it down to a feature of the code. It's an arguable point whether you'd want a date returned outside the start-end date range.

Maybe but you weren't consistent. 😉

Ok, I take your point, it's not comparing like with like.

There is a simple fix by wrapping the end date in the BETWEEN with EOMONTH:

GO
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the first day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
   FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',0,1)
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the first day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
  FROM dbo.DateTableTest dt
 WHERE DoMDate BETWEEN 'Jun 2015' AND '2021-10-15'
   AND dt.FirstDayOfMonth = 1
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the 3rd Thursday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
   FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',3,4)
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the 3rd Thursday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
  FROM dbo.DateTableTest dt
 WHERE DoMDate BETWEEN 'Jun 2015' AND  EOMONTH('2021-10-15')
   AND dt.WeekFromStart = 3
   AND dt.DoW = 4
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the last Tuesday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
   FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',5,2)
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the last Tuesday of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
  FROM dbo.DateTableTest dt
 WHERE DoMDate BETWEEN 'Jun 2015' AND  EOMONTH('2021-10-15')
   AND dt.WeekFromEnd = 1
   AND dt.DoW = 2
;
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (fn) Return the last day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
   FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',6,1)
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO
--===== (tb) Return the last day of each month for a range of months.
DECLARE @BitBucket DATE;
SELECT @BitBucket = DoMDate
  FROM dbo.DateTableTest dt
 WHERE DoMDate BETWEEN 'Jun 2015' AND EOMONTH('2021-10-15')
   AND dt.LastDayOfMonth = 1
GO 3
-----------------------------------------------------------------------------------------------------------------------
GO