Question 72328 in Mysql Question posted in
The official MySQL documentation can be found here.

how to put two columns value in between clause – Mysql

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…?

Tags:

2

Answers


  1. 0

    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')
    Login or Signup to reply.
  2. 0

    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search