SQL Query Needed - to Get data for previous date always, where date column is split into multiple column - Mysql

RajneshThakur
January 13, 2023
269 views
0 votes
2 Answers

I want to get rows where date is equal to Previous day,

Table Schema as below,

-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  xYear INTEGER,
  xMonth INTEGER,
  xDay INTEGER,  
);

— Sample Table —

id	name	gender	xYear	xMonth	xDay
1	Ryan	M	2023	1	12
2	Joanna	F	2023	1	12
3	ro	M	2023	1	11
4	han	F	2023	1	12
5	ta	M	2023	1	11
6	run	F	2023	1	11
7	radha	M	2023	1	12
8	cena	F	2023	1	12

—- Expected result —- considering today is 13th Jan, so i want data for 12th Jan—-

id	name	gender	xYear	xMonth	xDay
1	Ryan	M	2023	1	12
2	Joanna	F	2023	1	12
4	han	F	2023	1	12
7	radha	M	2023	1	12
8	cena	F	2023	1	12

not sure , how to achieve this.

Tags: mysql sql

Answers

- Jens
- January 13, 2023 at 8:27 am
- 0 votes
0
You can concat the columns to a stringand convert it to a date and compare it with "yesterday"
```
select  * 
from students 
where STR_TO_DATE(concat(xYear, '-',xMonth, '-',xDay), "%Y-%m-%d")=DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
```
Demo
Login or Signup to reply.

- JonasMetzler
- January 13, 2023 at 8:28 am
- 0 votes
0
It should be quite clear this is a bad DB structure and the date should be stored as date in one single column instead in future.

Creating the desired result can be achieved by building a date out of those three columns and then check this date is yesterday.

There are lots of functions which can do this job, here one way with CAST and CONCAT:
```
SELECT 
id, name, gender,  
xYear, xMonth, xDay
FROM students
WHERE 
  CAST(CONCAT(xYear,'-',xMonth,'-',xDay) AS DATE) 
    = CURDATE() - INTERVAL 1 DAY;
```
You can also try above without the CAST and check if this is executed faster:
```
SELECT 
id, name, gender,  
xYear, xMonth, xDay
FROM students
WHERE 
  CONCAT(xYear,'-',xMonth,'-',xDay) = CURDATE() - INTERVAL 1 DAY;
```
Try out here: db<>fiddle
Login or Signup to reply.