There is a table objects, which stores data on real estate objects. Me need to use a query to calculate a new field that will display the date range from Monday to Sunday, which includes the date the object was created (for example, “2020-11-16 - 2020-11-22”)

create table objects(
   object_id int NOT NULL PRIMARY KEY ,
city_id int not null ,
price int ,
area_total int ,
status varchar(50) ,
class varchar(50) ,
action varchar(50) ,
date_create timestamp,
FOREIGN KEY(city_id) references avg_price_square_city(city_id)
);

Data in the table:

INSERT INTO objects (object_id, city_id, price, area_total, status, class, action, date_create) 
   VALUES (1, 1, 4600000, 72, 'active', 'Secondary', 'Sale', '2022-05-12 21:49:34');
INSERT INTO objects (object_id, city_id, price, area_total, status, class, action, date_create) 
   VALUES (2, 2, 5400000, 84, 'active', 'Secondary', 'Sale', '2022-05-19 21:49:35');

The query should display two fields: the object number and a range that includes the date it was created. How can this be done ? P.S I wrote this query,but he swears at the "-" sign:

SET @WeekRangeStart  ='2022/05/10';
SET @WeekRangeEnd  = '2022/05/17';
select object_id,@range := @WeekRangeStart '-' @WeekRangeEnd
FROM objects where @range = @WeekRangeStart and date_create between @WeekRangeStart and @WeekRangeEnd
UNION
select object_id,@range from objects where @`range` = @WeekRangeEnd;

Error:[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@WeekRangeEnd FROM objects where @range = @WeekRangeStart and date_create betwee' at line 1

I want to receive in query:

object_id  @range
1          2022/05/10 - 2022/05/17

The column @range must contain the date from the "date_create"

CodePudding user response：

SET @WeekRangeStart  = CAST('2022/05/10' as DATE);
SET @WeekRangeEnd  = CAST('2022/05/17' as DATE);
SET @range = CONCAT(@WeekRangeStart,' - ',@WeekRangeEnd) ;
-- select @range;
select 
   object_id,
   @range
FROM objects 
where DATE(date_create) between @WeekRangeStart and @WeekRangeEnd
UNION
select object_id,@range from objects 
;

Gives next result:

• This result is the output of the SQL part that is put after the UNION. Because date_create is not between your WeekRangeStart and WeekRangeEnd.
  You should take some time, and read the UNION documentation.

• The variable @range is calculated before the SQL statement, because the value is a constant.

see: DBFIDDLE

NOTE: You should try to use the same dateformat everywhere, and not mix date like '2022-05-19 21:49:35' and 2022/05/10. Use - OR use /, but do not mix them...

EDIT: After the calirification "Me need to use a query to calculate a new field that will display the date range from Monday to Sunday,...":

You probably wanted to do:

SET @WeekDate = CAST('2022/05/10' as DATETIME);

SELECT 
   ADDDATE(@WeekDate, -(DAYOFWEEK(@WeekDate)-1)  1) as Monday,
   DATE_ADD(ADDDATE(@WeekDate, -(DAYOFWEEK(@WeekDate)-1)  9), INTERVAL -1 SECOND) as Sunday;

output: