1

I want to get all sunday date between two dates and get last day between two date, how can i do that in mysql? Example : 

start date | end date
2017-03-01 | 2017-03-31

then the results are : 

2017-03-05
2017-03-12
2017-03-19
2017-03-26
2017-03-31

Start date and end date possible to change, please advise me. Thank you

Mas Harjo
  • 73
  • 9

3 Answers3

0

MySQL has a DAYOFWEEK() function which would return 1 for any date which is a Sunday:

SELECT date_column
FROM yourTable
WHERE (date_column BETWEEN '2017-03-01' AND '2017-03-31' AND
       DAYOFWEEK(date_column) = 1) OR        -- any Sunday
      date_column = '2017-03-31'             -- or the last date in the range

I am assuming that yourTable already has dates in it. If you need help with populating a table with a range of dates, this problem has been covered well before on Stack Overflow, q.v. here:

How to populate a table with a range of dates?

Demo here:

Rextester

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • This assumes that all the dates are also in the table. Would be nice if he DOES have another table that has all the dates. – beejm Mar 30 '17 at 04:46
  • @BennjoeMordeno If he doesn't have any date data to begin with, then he should be asking about how to generate a calendar table. – Tim Biegeleisen Mar 30 '17 at 04:47
  • I assume that his table does not really have all the dates as per his given example; his example has a start date and end date in the same row. It's possible he's fine with accepting answers that are also procedures/batch statements? Could maybe generate a temp calendar table (or even a permanent one) – beejm Mar 30 '17 at 04:49
  • Hi @BennjoeMordeno, just like Tim said. I don't have table date in mysql, the start date and end date just choose by user. – Mas Harjo Mar 30 '17 at 04:54
  • @MasHarjo Then generate a calendar table to get your dates. A database might not be the fastest way to get this information, assuming you don't even have any tables at all setup. – Tim Biegeleisen Mar 30 '17 at 04:55
  • @BennjoeMordeno, please see my answers that the problem i face now – Mas Harjo Apr 04 '17 at 03:16
  • Hi @BennjoeMordeno please see at this question Thanks http://stackoverflow.com/questions/43108682/get-sunday-date-between-two-date-and-last-day-from-one-month-mysql – Mas Harjo Apr 04 '17 at 03:59
0

Since you now mentioned that you don't have a table for the dates, you could approach it like this if you don't want to add a generic calendar table on your database.

declare @startdate datetime 
declare @enddate datetime
DECLARE @startdateLoop datetime

select @startdate = CAST(start as DATE), @enddate = CAST(end_date as DATE) from #t
set @startdateLoop = @startdate
CREATE TABLE #tempCal
  (dates datetime)

  WHILE @startdateLoop != @enddate 
  BEGIN
  INSERT INTO #tempCal
  SELECT @startdateLoop

  SET @startdateLoop = DATEADD(dd, 1, @startdateLoop)
  END

  SELECT * FROM #tempCal
  WHERE dates between @startdate and @enddate and DAYOFWEEK(dates) = 1

You may turn this into a procedure if you want to.

Would still be nice if you DO HAVE a calendar table; as Tim has suggested.

beejm
  • 2,381
  • 1
  • 10
  • 19
0
SELECT calender FROM calender WHERE calender.calender >='2017-03-01' AND calender.calender <='2017-03-31'  AND DAYOFWEEK(calender) = 1 OR calender = '2017-03-31' Group By calender

This is @Tim Answer Thanks Tim

Mas Harjo
  • 73
  • 9