按时间删除数据报错的解决办法(Incorrect datetime value: '%2017-07-31%' for column 'orderDate' at row 1)
来源:互联网 发布:淘宝子账号怎么用 编辑:程序博客网 时间:2024/05/23 20:03
当按某个时间删除数据时,如果该时间是datetime类型,而条件语句里面比较的对象是date类型(XXXX-XX-XX)时,若服务器数据库版本较高,继续这样删除数据时会出现如下错误:
org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [DELETE FROM ct_ppv_order WHERE orderDate like '%2017-07-31%']; Data truncation: Incorrect datetime value: '%2017-07-31%' for column 'orderDate' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '%2017-07-31%' for column 'orderDate' at row 1
解决办法:
利用date_format(orderDate,'%Y-%m-%d')
将datetime型的数据转为date形式,不再用like去比较
eg:
//将订购时间为该批次统计时间的数据都清空String sql2 = "DELETE FROM ct_ppv_order WHERE date_format(orderDate,'%Y-%m-%d') = '" + beforeNowDateToString + "'";
将orderDate用date_format()转换为date类型,再和条件语句里的beforeNowDateToString 作比较。
阅读全文
0 0
- 按时间删除数据报错的解决办法(Incorrect datetime value: '%2017-07-31%' for column 'orderDate' at row 1)
- MySql Incorrect datetime value: '' for column 'dateTime' at row 1
- Incorrect datetime value: '' for column 'log_time' at row 1
- Incorrect datetime value: '' for column 'ordertime' at row 1
- jbpm 4.4 启动流程实例报错:Data truncation: Incorrect datetime value: '' for column 'START_' at row 1
- 解决Incorrect datetime value: '' for column 'time' at row 1的问题
- Data truncation: Incorrect datetime value: '' for column 'start' at row 1的解决方法
- 关于解决Incorrect datetime value: '' for column '' at row 1的问题
- Data truncation: Incorrect datetime value: '' for column 'xxxx' at row
- mybatis 插入数据报错:Incorrect string value: '\xE4\xBC\x9A\xE5\x91\x98' for column 'recipient' at row 1
- mybatis 插入数据报错:Incorrect string value: '\xE4\xBC\x9A\xE5\x91\x98' for column 'recipient' at row 1
- Warning (Code 1366): Incorrect string value: '\' for column 'msg' at row 1的解决办法
- 该错误的解决办法:Incorrect string value: '\xF0\x9F...' for column 'XXX' at row 1
- mysql报错问题:Incorrect integer value: ” for column ‘num’ at row 1
- MySQL报错“ Incorrect integer value: '' for column 'XXXX' at row 1 ”
- Mysql 字符串保存报错Incorrect string value: '...' for column 'DepartmentName' at row 1
- 【SSH网上商城】Incorrect datetime value: '' for column 'pdate' at row 1
- 【SSH网上商城】Incorrect datetime value: '' for column 'ordertime' at row 1
- Android Studio 如何多项目共用和维护统一模块
- Arm汇编学习笔记(五)——MOVS及^符号等
- 用于并行计算的多线程数据结构,第 2 部分: 设计不使用互斥锁的并发数据结构
- 奋战聊天机器人(五)从一句话中提取更多信息
- XML文件结构和基本语法
- 按时间删除数据报错的解决办法(Incorrect datetime value: '%2017-07-31%' for column 'orderDate' at row 1)
- 二分查找
- 观察者模式随笔
- 【jQuery】网页文本格式编辑器xheditor
- Oracle 删除重复数据只留一条
- 《终极网络服务端编程》的lua server 源码
- spring04
- iOS之JSPatch
- Azkaban-2.5.0的安装和配置