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

Question

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

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I need a SQL command (SQLite for android) that returns the one-week interval (from Sunday to Saturday) of an informed date. For example: I enter the date '05 -05-2016 ', hence the command in sql would return 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 make it clearer still kkkk), I inform the date '01 -04-2016 ', hence the function in sql would return '27 -03-2016' and '02 -04-2016 '(corresponding from Sunday to Saturday of the informed date).

mysql sql sqlite

asked by anonymous 05.05.2016 / 17:24

2 answers

C # - Access Denied Windows Files Android Delphi XE8 Home screen

score 1 · Answer 1

One possible solution would be this:

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

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

The select returns:

2016-05-15 2016-05-21

score 1 · Answer 2

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

Stay the SQLFiddle

SQLite solution

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

There's also the SQLFiddle