Inform a date, and catch the interval of the week of the month of that date (from Sunday to Saturday)

Asked

Viewed 706 times

2

I am in need of an SQL command (Sqlite for android), which returns the interval of one week (from Sunday to Saturday) of an informed date. For example: inform the date '05-05-2016', hence the sql command would return me the range '01-05-2016' and '07-05-2016' (from Sunday to Saturday) which corresponds to the week of the date informed. Another example (to be clearer still kkkk), I inform the date '01-04-2016', hence the sql function would return me '27-03-2016' and '02-04-2016' (which corresponds from Sunday to Saturday, the date informed).

mysql sql sqlite

  • Would that be more or less? http://answall.com/questions/76146/mostrar-todos-dias-entre-duas-datas/76150#76150 . This example here is in SQL-Server.

  • It would not be that Marconi, I need only the beginning date of a week (Sunday) and the end date of this week (Saturday). As in the example, which I mentioned... I inform '05-05-2016', hence I have the return '01-05-2016' and '07-05-2016' (first day of the week of the informed date (Sunday) and the last day of the week

  • 1

    Shouldn’t this type of calculation stay in the application? Because it is passing this responsibility to the database?

  • I’m a beginner in SQL, I know almost nothing yet, but I’m making a "filter" to take the dates corresponding to the week in the format I spoke about (Sunday to Saturday of the date mentioned), and make a sum of the data of this date range.

2 answers

1

A solution for Mysql is to use the functions DAYOFWEEK and DATEADD, for example:

SELECT  ADDDATE('2016-05-05', INTERVAL 1 - DAYOFWEEK('2016-05-05') DAY) Domingo
       ,ADDDATE('2016-05-05', INTERVAL 7 - DAYOFWEEK('2016-05-05') DAY) Sabado
       ,ADDDATE('2016-04-01', INTERVAL 1 - DAYOFWEEK('2016-04-01') DAY) Domingo
       ,ADDDATE('2016-04-01', INTERVAL 7 - DAYOFWEEK('2016-04-01') DAY) Sabado

The result would be

Domingo     Sabado      Domingo     Sabado
2016-05-01  2016-05-07  2016-03-27  2016-04-02

That’s where the Sqlfiddle

Solution for Sqlite

select DATE('2016-05-05', 'weekday 0', '-7 days') Domingo,
       DATE('2016-05-05', 'weekday 0', '-1 days') Sabado,
       DATE('2016-04-01', 'weekday 0', '-7 days') Domingo,
       DATE('2016-04-01', 'weekday 0', '-1 days') Sabado

The Sqlfiddle

  • Thanks for the reply also Bruno seems to work well too, I will test-there.

1

A possible solution would be this:

In this example the date of entry is 19/05/2016

SELECT date('2016-05-19', '-6 day', 'weekday 0') as anterior, date('2016-05-19', 'weekday 6') as posterior;

select returns:

2016-05-15 2016-05-21

  • Thank you Reginaldo, your answer worked!!! D.

Browser other questions tagged mysql sql sqlite

You are not signed in. Login or sign up in order to post.