Convert column(s) data type temporally to run a new query on the temporal column data type

This was my SQL query:

SELECT DISTINCT Booked_From, Booked_To FROM bookings 
WHERE CONVERT(DATETIME,Booked_From,101) AND CONVERT(DATETIME,Booked_To,101) 
AND Hallname ='Executive Hall' 
AND Booked_From >= '11/26/2021' 
AND Booked_To <= '12/16/2021' 
AND Bin='false'  
ORDER BY str_to_date(Booked_From,'%m/%d/%Y') ASC

And this was the error generated: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Booked_From,101) AND CONVERT(DATETIME,Booked_To,101) AND Hallname ='Executive...' at line 1

Booked_From & Booked_To are VARCHARs

Thanks

CodePudding user response：

First of all, you should save dates always in MySQL in the form that is normal for MySQL yyyy-mm-dd. Because every conversion takes time

So in Mysql your query would be

SELECT DISTINCT Booked_From, Booked_To FROM bookings 
WHERE 
Hallname ='Executive Hall' 
AND str_to_date(Booked_From,'%m/%d/%Y') >= str_to_date('11/26/2021','%m/%d/%Y') 
AND str_to_date(Booked_To,'%m/%d/%Y')  <= str_to_date('12/16/2021','%m/%d/%Y') 
AND Bin='false'  
ORDER BY str_to_date(Booked_From,'%m/%d/%Y') ASC;

But it would also be better to compare dates in MySQL style

SELECT DISTINCT Booked_From, Booked_To FROM bookings 
WHERE 
Hallname ='Executive Hall' 
AND str_to_date(Booked_From,'%m/%d/%Y') >= '2021-11-26'
AND str_to_date(Booked_To,'%m/%d/%Y')  <= '2021-12-16'
AND Bin='false'  
ORDER BY str_to_date(Booked_From,'%m/%d/%Y') ASC