将Varchar类型日期转换并查询指定范围订单数据

问题 #

数据库orders表的date字段为Varchar类型，存储格式为日-月-年（如28-12-2022），需查询2022年4月1日至2023年3月3日期间的高风险订单数据，尝试转换日期格式的SQL语句未生效。

最初尝试的无效SQL #

SELECT fname, emailid, highrisk, count(*),sum(amount) 
FROM member 
JOIN orders ON member.sno =orders.customerid 
WHERE highrisk LIKE 'Yes' AND convert(datetime, date, 105) between '01-04-2022' and '31-03-2023'
GROUP BY fname, emailid, highrisk;

最终可用的SQL方案 #

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-03-03' 
GROUP BY fname, emailid, highrisk;

关键说明 #

• 原语句使用的convert(datetime, date, 105)是SQL Server的日期转换语法，若使用MySQL需改用str_to_date()函数，格式符%d-%m-%Y匹配日-月-年的存储格式。
• 查询条件中的日期使用YYYY-MM-DD标准格式，避免因日期格式歧义导致的查询错误。
• 新增orders.status='completed'条件（根据最终生效语句补充），可按需调整或移除。

内容的提问来源于stack exchange，提问作者user2741620