Jaypee Hindang Jaypee Hindang - 3 months ago 6
MySQL Question

mysql query get 1st specific day between 2 dates

How do I get the first Sunday between 2 dates?
i have only 2 fields in myTable (dt_from and dt_to)

dt_from = '2016-08-6';
dt_to = '2016-08-19';

SELECT firstsunday FROM myTable BETWEEN dt_from AND dt_to;

Answer

How about getting the first Sunday just after from date itself?

SELECT DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) from myTable as A;

http://sqlfiddle.com/#!9/7fce5

If Sunday isn't between the dates:

SELECT DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) as firstsunday from myTable as A where DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) between A.date_from and A.date_to;

http://sqlfiddle.com/#!9/83d0f0/4

A little bit shorter:

SELECT firstsunday from (SELECT A.date_from, A.date_to, DATE_ADD(A.date_from, INTERVAL (6 - WEEKDAY(A.date_from)) DAY) as firstsunday from myTable as A) as B where B.firstsunday between B.date_from and B.date_to;

http://sqlfiddle.com/#!9/6adab3/1

Comments