Oracle迁移mysql修改语法时,发现日期比较出现报错,原因是date_format函数返回字符集
- 报错片段,其中BEGIN_DATE是data_time类型
SELECT * FROM xxx a where date_format('2018/11/1', '%Y-%m') < a.BEGIN_DATE;
- 官方解释
DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters.
- 直接说方案吧
方案一:两边都date_format
SELECT * FROM xxx a where date_format('2018/11/1', '%Y-%m') < date_format(a.BEGIN_DATE, '%Y-%m-%d');
方案二:前面的常量format字符串加上“日”,’%Y-%m-%d’
SELECT * FROM xxx a where date_format('2018/11/1', '%Y-%m-%d') < a.BEGIN_DATE;
方案三:表字段放在前面
SELECT * FROM xxx a where a.BEGIN_DATE > date_format('2018/11/1', '%Y-%m') ;