SQL Server 2012 Date Format – Determine 3rd Friday of Each Month

You don't want to use BETWEEN to satisfy this query for the reasons I outline in this blog post:

• https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

Instead you want an open-ended date range. Begins on the first of the month in question, and is less than the next month. To determine the first of the month given any date, you can do this:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

And to get first of the the following month, just add one:

SELECT DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, GETDATE()), 0);

Of course if you want last month only on the first of the current month, and this month after the first, then you can say:

SELECT DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())), 0);

So for your query, if you always want it relative to the current month:

...
FROM SupportTracker.dbo.ViewIssueListwBugTypeNDevice AS v
WHERE NOT EXISTS 
(
  SELECT 1 FROM SupportTracker.dbo.DashboardRecords
    WHERE bg_id = v.bg_id
)
AND v.bg_reported_date >= DATEADD(MONTH, 0, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE()))
AND v.bg_reported_date < DATEADD(MONTH, 1, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE()));

Or if you want to take a date as an optional parameter, you don't need to subtract a day, just pass in any date within the month you actually want (and if you pass in NULL, it will still fall back to the above formula):

  @Month DATE = NULL
...

  SET @Month = DATEADD(MONTH, 0, DATEDIFF(MONTH, 0, COALESCE(@Month, 
    DATEADD(DAY, -1, GETDATE())));  

...
FROM SupportTracker.dbo.ViewIssueListwBugTypeNDevice AS v
WHERE NOT EXISTS 
(
  SELECT 1 FROM SupportTracker.dbo.DashboardRecords
    WHERE bg_id = v.bg_id
)
AND v.bg_reported_date >= @Month
AND v.bg_reported_date < DATEADD(MONTH, 1, @Month);

Generally, though, the following is better to determine the given month, because datediff can cause issues:

SELECT FirstOfThisMonth = DATEADD(DAY, 1-DAY(GETDATE()), CONVERT(date, GETDATE()));

It is possible that I am missing something, but you should be able to get the result easily using the max() aggregate and a GROUP BY:

select
  max(date) maxdate,
  year
from yourtable
group by year;

See SQL Fiddle with Demo