oracle pl-sql find the 3rd wednesday of a given date
Currently I have a process which takes a date variable EG: '01-MAR-2026', it then iterates over this date checking if the day is wednesday. If so then increment a counter else add 1 day and check again. This exits when the wednesday count is = 3.
IF (to_char(v_Date,'dy') = 'wed') THEN v_NumWed := v_NumWed + 1; END IF; WHILE (v_NumWed != 3) LOOP v_Date := v_Date + interval '1' day; IF (to_char(v_Date, 'dy') = 'wed') THEN v_NumWed := v_NumWed + 1; END IF; END LOOP;
However I have a feeling there is a simpler way to achieve this maybe via a query or something not using a loop EG: use an input date and query to find the first wednesday then + 14 days?
Any suggestions would be great, thanks in advance :)
EDIT: I have something like this which appears to work, also considers if the first day is a wednesday itself.
select next_day(trunc(v_date, 'MM') - 1, 'WED'), next_day(trunc(v_date, 'MM') - 1, 'WED') + 14 into v_first, v_third from dual;
SOLVED: thanks for the help folks, got a few possible solutions now in the comments!
TO get the third Wednesday from a date, including that date if ti is itself a Wednesday, you can do:
select next_day(date '2026-03-01' + 13, 'Wednesday') from dual; NEXT_DAY(D ---------- 2026-03-18
next_day function is a bit awkward because it is NLS-sensitive, and doesn't have an override (like
to_char() does) - so the NLS session language and the day name/abbreviation have to be in the same language. If you can't control then then you can work around it by getting the current-language day-name from a known fixed date:
alter session set nls_date_language = 'French'; select to_char(date '2026-03-01', 'Day'), next_day(date '2026-03-01' + 13, to_char(date '1999-12-29', 'Day')) from dual; TO_CHAR( NEXT_DAY(D -------- ---------- Dimanche 2026-03-18
Demo with some sample dates:
with t (sample_date) as ( select date '2026-02-15' + level from dual connect by level <= 31 ) select sample_date, next_day(sample_date + 13, 'Wednesday') as third_wed from t order by sample_date; SAMPLE_DAT THIRD_WED ---------- ---------- 2026-02-16 2026-03-04 2026-02-17 2026-03-04 2026-02-18 2026-03-04 2026-02-19 2026-03-11 2026-02-20 2026-03-11 2026-02-21 2026-03-11 2026-02-22 2026-03-11 2026-02-23 2026-03-11 2026-02-24 2026-03-11 2026-02-25 2026-03-11 2026-02-26 2026-03-18 2026-02-27 2026-03-18 2026-02-28 2026-03-18 2026-03-01 2026-03-18 2026-03-02 2026-03-18 2026-03-03 2026-03-18 2026-03-04 2026-03-18 2026-03-05 2026-03-25 2026-03-06 2026-03-25 2026-03-07 2026-03-25 2026-03-08 2026-03-25 2026-03-09 2026-03-25 2026-03-10 2026-03-25 2026-03-11 2026-03-25 2026-03-12 2026-04-01 2026-03-13 2026-04-01 2026-03-14 2026-04-01 2026-03-15 2026-04-01 2026-03-16 2026-04-01 2026-03-17 2026-04-01 2026-03-18 2026-04-01
Ask TOM "How to find out if the date is a weekend or , I'm having trouble figuring out how to find out if the date is a weekday or a weekend from a column. Day, then generate the results to date_desc of table B using PL/SQL block? SQL> CREATE TABLE t 2 (id int, 3 d date); Table created. 18 10-MAY-17 19 11-MAY-17 20 12-MAY-17 20 rows selected. Oracle SQL Day Of Week. Occasionally we need to find out the day of week from a given date. This is also achieved throug TO_CHAR function with appropriate date flag ‘D’. Specifically the expression below can be used to get the day of week from date value. to_char(date_value,’D’)
How about this? Find the first Wednesday (using
NEXT_DAY) that follows some date, and add 14 days (i.e. 2 weeks) to find the 3rd Wednesday.
SQL> alter session set nls_date_format = 'dd.mm.yyyy'; Session altered. SQL> alter session set nls_date_language = 'english'; Session altered. SQL> WITH test (col) 2 AS (SELECT DATE '2020-01-27' FROM DUAL 3 UNION ALL 4 SELECT DATE '2020-02-06' FROM DUAL 5 UNION ALL 6 SELECT DATE '2020-02-12' FROM DUAL) 7 SELECT col, NEXT_DAY (col, 'wed') + 14 next_3 8 FROM test; COL NEXT_3 ---------- ---------- 27.01.2020 12.02.2020 06.02.2020 26.02.2020 12.02.2020 04.03.2020 SQL>
If comment you posted means that - for any date - you want to find 3rd Wednesday in that month, then truncate date to month (which returns 1st day in that month) and find the 3rd Wednesday, taking care about whether the 1st day of the month is (or is not) Wednesday.
SQL> WITH test (col) 2 AS (SELECT DATE '2020-01-27' FROM DUAL 3 UNION ALL 4 SELECT DATE '2020-02-06' FROM DUAL 5 UNION ALL 6 SELECT DATE '2020-03-18' FROM DUAL) 7 SELECT col, 8 NEXT_DAY (TRUNC (col, 'mm'), 'wed') 9 + CASE 10 WHEN TO_CHAR (TRUNC (col, 'mm'), 'dy') = 'wed' THEN 7 11 ELSE 14 12 END 13 next_3 14 FROM test; COL NEXT_3 ---------- ---------- 27.01.2020 15.01.2020 06.02.2020 19.02.2020 18.03.2020 18.03.2020 SQL>
WeekDay, Use this function to determine the day of the week in a given date and return the Sunday. 2. Monday. 3. Tuesday. 4. Wednesday. 5. Thursday. 6. Friday. 7. Date. Enter a valid date string. The system assumes your entry is in the format specified by the Format parameter. The default is the current date. Format. Enter a valid date format that describes the format used by your entry in the Date parameter. The default is date format 1 (MM/DD/YY). Locale (Optional) Enter the locale code.
I know you already solved the issue, just wanted to show you another example:
DECLARE dStartDate DATE := SYSDATE; dNextWednesDay DATE; BEGIN FOR n IN 1..3 LOOP dNextWednesDay := NEXT_DAY(dStartDate, 'WEDNESDAY'); DBMS_OUTPUT.PUT_LINE(dNextWednesDay); dStartDate := dNextWednesDay +1; END LOOP; END; /
2020-02-19 15:33:58 2020-02-26 15:33:58 2020-03-04 15:33:58
Ask TOM "Displaying week days for current week", I would like to output all weekdays (monday-friday) of the current week. SQL> exec :x := '26-SEP-2016' PL/SQL procedure successfully completed. SQL> SQL> select 2 next_day(to_date(:x,'DD-MON-YYYY'),'MON') + 3 case days) starting with a given date and having week count always start with 1? I want the sql to get the start of the week date and end of the week date for a given date and my week starts from saturday and ends with friday. Example, if the given date is 03/mar/2018 then start date is - 03/mar/2018 and end date is 09/mar/2018 if the given date is 04/mar/2018 then start date is - 03/mar/2018 and end date is 09/mar/2018
Oracle NEXT_DAY Function By Practical Examples, function to get the date of the next weekday which is later than a specified date. that evaluates to a DATE value which is used to find the next weekday. Thanks for the question, Cindy. Asked: January 22, 2003 - 4:48 pm UTC. Answered by: Tom Kyte - Last updated: August 31, 2005 - 2:02 pm UTC
ORACLE LAST_DAY() Function By Practical Examples, This tutorial shows you how to use Oracle LAST_DAY() function to get the last day of the month of a specified date and gives you some practical examples. PL/SQL; Functions The Oracle LAST_DAY() takes a DATE argument and returns the last day of the SELECT LAST_DAY( SYSDATE ) - SYSDATE FROM dual;. Below I am giving two examples to get quarter from the date in Oracle using PL/SQL and SQL query. Get Quarter Using PL/SQL Function (Stored Function) in Oracle The following function takes the date parameter and returns the quarter as the number.
I have a queried date set which contains several fields including a column of dates. What I want to do is create a new field in my query that tells what the Monday and Friday is for the week of that row's particular date. So for example; if the date in one of my rows is "1/16/18", the new field should indicate "1/15/18 - 1/19/18".
- So for 2026-03-01 you're expecting result 2026-03-18, right? What if the original date is itself a Wednesday - should 2026-03-04 also map to 2026-03-18, or 2026-03-25?
- Alex - good test case, I think the edited code at the bottom covers it. 01-APR-20 is a wednesday and goes into v_first. the 15th is the 3rd wednesday and goes into v_third. Not essentially graceful looking but it works
- thanks Alex, realized I should have explained the data set a bit more. the start day will always be 01, the month will always be either Mar, Jun, Sep, Dec but the year can be any year from current onwards
- @C-MATT - this still works, doesn't it? You'll just won't encounter most of the dates. That really isn't what your question says, but your approach should be OK too; though the
trunc()looks a bit pointless, and you can do +13 still instead of -1 + 14. Same basic idea.
- seen your updates, forgot about the NLS_ impact... original code check the day of week = 4 which only works for USA as their day 1 is sunday so day 4 is wednesday. this code will be run globally potentially so the oracle nls_database settings are set to America for territory, language and date language. do you think that is ok
- goal is 3rd wednesday of a month starting from the 1st of that month, can't spill over into next month EG: not a rolling every 3rd wednesday :)
- Aha; so, depending on date, you want to find 3rd Wednesday in that month. I edited my answer and added some more code. See if it helps.
- yeh that was my bad sorry, that new code looks pretty good I'll have a play around withat as well, thanks for the help!