SQL | Convert varchar to date in WHERE clause

Other SQL Server Topics
1

Good day,

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)
FROM member
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

2

Ideally you should change the data type to a date; there are no good options with a date held as a string.

If you really have to use a string, and no suitable Calendar Table with a string column is available, try using a Date Range TVF. eg

SELECT M.fname, M.emailid, SUM(O.amount) AS amount
FROM Member M
	JOIN Orders O
		ON M.sno = O.customerid
	JOIN dbo.DateRange('20220401', '20230331', 'dd', 1) D
		ON O.[date] = CONVERT(char(10), D.[value], 105)
WHERE M.highrisk = 'Yes'
GROUP BY M.fname, M.emailid;
3

Hiya, thanks for the reply.

So after much testing, we managed to come up with the following SQL query:

> SELECT fname, emailid, highrisk, count(*),sum(amount) 
> FROM member JOIN orders ON member.sno =orders.customerid 
> WHERE highrisk LIKE 'Yes' 
> AND orders.status='completed' 
> AND str_to_date(date,'%d-%m-%Y') >= ' 2022-04-01' 
> AND str_to_date(date,'%d-%m-%Y') <= ' 2023-04-30' 
> GROUP BY fname, emailid, highrisk;
4

Thanks a lot for information, was searching for similar one! Great!