The First Wednesday and Last Thursday of EVERY month in BigQuery

I am looking to pull the first Wednesday of EVERY month starting for the first day of the month.

SELECT
 CASE
 WHEN latestRefreshTime => ***first wednesday of every month*** THEN 'Up to Date'
 WHEN latestRefreshTime2 => ***last thursday of every month*** THEN 'Up to Date'
ELSE ' Not Up To Date'
END AS upToDate

I have tried using DATE_TRUNC(CURRENT_DATE(), MONTH) to get the first day of the month, but I am having a hard time drilling it to down to the first wednesday and for it to be dynamic so it changes every month. The same thing for the last Thursday

Hope below BigQuery example will help you

WITH days AS (
  SELECT CURRENT_DATE AS dt UNION ALL
  SELECT '2023-05-22' UNION ALL
  SELECT '2023-04-22' UNION ALL
  SELECT '2023-03-22' 
)
SELECT dt, 
  IF(EXTRACT(MONTH FROM x) = EXTRACT(MONTH FROM DATE(dt)), x, x - 7) as last_thursday,
  IF(EXTRACT(MONTH FROM y) = EXTRACT(MONTH FROM DATE(dt)), y, y + 7) as first_wednesday
FROM(
  SELECT dt, 
    LAST_DAY(LAST_DAY(DATE(dt), MONTH), WEEK(FRIDAY)) x,
    LAST_DAY(LAST_DAY(DATE_TRUNC(dt, MONTH) - 1, MONTH), WEEK(THURSDAY)) y
  FROM days
) 

with output

You can create an intermediate table with the first Wednesday and last Thursday of the month of the respective date and then aplly the rule.

DECLARE arbitrary_date DATE DEFAULT'2023-06-21';

WITH
-- Table with first and last day of the month for each date
first_last_day_table AS (
  SELECT
    arbitrary_date,
    DATE_TRUNC(arbitrary_date, MONTH) AS first_day_month,
    DATE_SUB(DATE_TRUNC(DATE_ADD(arbitrary_date, INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY) AS last_day_month,
)

-- Table with first wednesday and last thursday for each date
SELECT
  arbitrary_date,
  first_day_month + MOD(11 - EXTRACT(DAYOFWEEK FROM first_day_month), 7) AS first_wednesday,
  last_day_month - MOD(2 + EXTRACT(DAYOFWEEK FROM last_day_month), 7) AS last_thursday
FROM first_last_day_table