Mysql Select find third week and day of week from table date

29
April 16, 2019, at 01:20 AM

I have a MySQL select query that I am trying to get the third Thursday from a date in my table here is what I have for the query any help would be greatly appreciated.

SELECT 
trandate,
transno,
inputdate,
DATE_ADD(DATE(trandate),
    INTERVAL (IF(DAYOFWEEK(trandate) < 5,
        (3 + WEEK(trandate)),
        WEEK(trandate))) DAY) AS third_thursday
FROM
    ts.debtortrans
WHERE
    trandate = '2019-03-01';
Answer 1

If you want the third Thursday after the date, you can get that by adding 3 weeks, then a number of days to adjust the date to a Thursday. This is computed using

(12 - DAYOFWEEK(trandate)) % 7

We need to use 12 rather than 5 (DAYOFWEEK for Thursday) as MySQL returns a negative number for a modulo operation where the dividend operand is negative.

So the formula is

trandate + INTERVAL 3 WEEK + INTERVAL ((12 - DAYOFWEEK(trandate)) % 7) DAY

Demo on dbfiddle

READ ALSO
SQL query for joining four tables

SQL query for joining four tables

I am a newbie to writing SQL queries, Can anyone please help me write SQL query for below conditions and joins?

38
Versioning on a table with foreign keys

Versioning on a table with foreign keys

Let's suppose I have 3 tables,

49
MySQL count all records that have at least one record on a pivot table

MySQL count all records that have at least one record on a pivot table

I'm trying to look if a record of a table, has at least one record on a pivot table that is used on a many to many relationship

42
table inner join not displaying expected results

table inner join not displaying expected results

I am trying to get a query to display some resultsI double checked the logic to make sure that it is correct and it looks like it should work to me but does not

26