My date format in Database is in varchar, and I need to get a range of order data from 1st April 2022 to 3rd March 2023. The column name is called date
The date records are written like this in varchar: "28-12-2022"
I understand to get a range of date, I need to convert varchar to date format. I searched through google and found many SQL syntax suggestions, but I could not get it to work.
We are using phpmyadmin, and there are 2 Tables; members & orders
Basically, What we are doing in the SQl is to filter all members who are considered "High Risk", count how many orders they made and sum their orders from 1st April 2022 to 3rd March 2023. The date is where I am having issue since it is not in datetime format. I am not the original SQL admin, so "date" was already varchar, I don't know what it will affect if I change the datatype.
SELECT fname, emailid, highrisk, count(*),sum(amount)
INNER JOIN orders ON member.sno = orders.customerid
WHERE highrisk = 'Yes' AND
convert(datetime, orders.date, 105) between '01-04-2022' and '31-03-2023'
GROUP BY fname, emailid, highrisk;
Running this SQL give me the error:
#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 'orders.date, 105) between '01-04-2022' and '31-03-2023'
GROUP BY fname, emailid' at line 5