how to put two columns value in between clause

Question

I was trying to get a data between yesterday's noon to today's noon.

There are two columns for time, one is called in_date(date type) and the other one is called in_time(time type).

At first, i tried to put those values in between values, but I did know what to put time values for between.

This is what i did

SELECT b.filepath, b.filename, a.indate, a.intime
FROM tb_st AS a
INNER JOIN tb_cont AS b ON a.stuidx = b.stuidx
WHERE (a.indate BETWEEN '2023-04-26' AND '2023-04-27')

then I decided to combine in_date and in_time but, I failed again....it says unknown column 'newdate' in 'where clause'

This was my second try..

SELECT CAST(a.indate AS DATETIME) + CAST(a.intime AS DATETIME) AS newdate,
       b.filepath, b.filename, a.indate, a.intime
FROM tb_st AS a
INNER JOIN tb_cont AS b ON a.stuidx = b.stuidx
WHERE newdate BETWEEN '20230426120000' AND '20230427120000'

what is the best way to get a result for this...?

It's because you are using alias in where clause: [link](https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error#:~:text=You%20can%20only%20use%20column%20aliases%20in%20GROUP%20BY%2C%20ORDER%20BY%2C%20or%20HAVING%20clauses.) — Rahid Zeynalov, Apr 27 '23 at 07:27

score 1 · Answer 1 · answered Apr 27 '23 at 07:31

It doesn't need to be complicated.

You have two columns: in_date and in_time
You want to find records from 2023-04-26 12:00 to 2023-04-27 11:59:59

So the WHERE clause can be like:

WHERE (a.in_date = '2023-04-26' AND a.in_time >= '12:00') OR (a.in_date = '2023-04-27' AND a.in_time < '12:00')

score 0 · Answer 2 · answered Apr 27 '23 at 07:32

You can try it as:

select * from (
        SELECT CAST(a.indate AS DATETIME) + CAST(a.intime AS DATETIME) AS newdate, 
               b.filepath, 
               b.filename, 
               a.indate, 
               a.intime
        FROM tb_st AS a 
            INNER JOIN tb_cont AS b ON a.stuidx = b.stuidx ) x 
WHERE x.newdate BETWEEN '20230426120000' AND '20230427120000'

how to put two columns value in between clause

2 Answers2