NutzCN Logo
问答 时间查询时,转换函数怎么写
发布于 198天前 作者 qq_297125b4 302 次浏览 复制 上一个帖子 下一个帖子
标签:

是不是只能这样?

cnd.and(new Static("create_time >= str_to_date('" + freeze.getBeginTime() + "','%Y-%m-%d %H:%i:%s')"));
31 回复

传Date作为参数啊

我之前传date,但是sql还是:create_time >= '2018-02-01 09:46:55' 这样的,str_to_date才能走索引吧?

不影响的话,传字符串不是也行?还需要转date吗

cnd.and("create_time", ">", date);

@wendal cnd.and("create_time", ">", LocalDate); 会出问题 . Java8的日期

用普通的date

今天也遇到了这个问题,请问一下各位大佬你们怎么解决的?
Sat Feb 24 2018 16:12:30 GMT+0800 (中国标准时间) 传递到后台的时间是这样的,数据库为oracle

用java.util.Date

@wendal 兽总好,我用的是java.util.Date

if (!DataTypeUtil.isEmptyStr(pm.get("startDate")) && !DataTypeUtil.isEmptyStr(pm.get("endDate"))) {
			cnd.and("CREATE_TIME", ">=", Date.parse((String) pm.get("startDate")));
			cnd.and("CREATE_TIME", "<=", Date.parse((String) pm.get("endDate")));
		}

代码如上,但是还是不行,是不是我还少了什么转换啊?

如何不行?报错?

@wendal sql语句打印在下面,数据库里面用的是Date类型存储时间,但是还是报错,我怎么在上面的代码里面进行to_date转换啊?

SELECT COUNT(*) FROM CW_VOUCHER  WHERE company_id=2 AND VOUCHER_TYPE_ID=336 AND CREATE_TIME >= '2018-02-17 16:31:20' AND CREATE_TIME <= '2018-02-24 16:31:23'

@wendal 对,报错,我在上面的基础上又用SimpleDateFormat 进行了一次转换才达到sql语句中的样子

贴报错信息,日志里面打印的是示意sql

报错信息:ORA-01861: 文字与格式字符串不匹配
因为数据库中存储的格式是这样的:2018/2/22 星期四 下午 5:34:07
@wendal

贴完整代码

来自炫酷的 NutzCN

完整代码及完整报错信息

来自炫酷的 NutzCN

代码:

if (!DataTypeUtil.isEmptyStr(pm.get("startDate")) && !DataTypeUtil.isEmptyStr(pm.get("endDate"))) {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			cnd.and("CREATE_TIME", ">=", format.format(Date.parse((String) pm.get("startDate"))));
			cnd.and("CREATE_TIME", "<=", format.format(Date.parse((String) pm.get("endDate"))));
		}

报错信息:

org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT COUNT(*) FROM CW_VOUCHER  WHERE company_id=2 AND VOUCHER_TYPE_ID=336 AND CREATE_TIME >= '2018-02-17 10:33:50' AND CREATE_TIME <= '2018-02-24 10:33:52''
PreparedStatement: 
'SELECT COUNT(*) FROM CW_VOUCHER  WHERE company_id=? AND VOUCHER_TYPE_ID=? AND CREATE_TIME >= ? AND CREATE_TIME <= ?'
CaseMessage=ORA-01861: 文字与格式字符串不匹配

	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:104)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:158)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:125)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:92)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:81)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:240)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:252)
	at org.nutz.dao.impl.NutDao._count(NutDao.java:725)
	at org.nutz.dao.impl.NutDao.count(NutDao.java:687)
	at com.sys.framework.result.miniui.MIniUIGridResultMap.<init>(MIniUIGridResultMap.java:35)
	at com.fzzl.hrp.cw.zcpd.service.CwDisbursementVoucherService.queryAll(CwDisbursementVoucherService.java:129)
	at com.fzzl.hrp.cw.zcpd.controller.CwDisbursementVoucherAction.queryAll(CwDisbursementVoucherAction.java:205)
	at com.fzzl.hrp.cw.zcpd.controller.CwDisbursementVoucherAction$FM$queryAll$0775d72a02af5a2c41e18c0373b573df.invoke(CwDisbursementVoucherAction.java)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:30)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:202)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:498)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:796)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1368)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)

@qq_32b899df 用的是oracle数据库,我不知道在上述代码里面怎么进行to_date转换。。

直接< data对象不行嘛?

不行,试过了,还是没用

不要format啊

来自炫酷的 NutzCN

nutz自动适配数据库的,传data框架层面帮你转换条件

@zhangkaidsy 可加社区群 68428921 ? 需要我远程你嘛?

不要format的话,就换了一个报错

org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT COUNT(*) FROM CW_VOUCHER  WHERE company_id=2 AND VOUCHER_TYPE_ID=336 AND CREATE_TIME >= 1518835314000 AND CREATE_TIME <= 1519440119000'
PreparedStatement: 
'SELECT COUNT(*) FROM CW_VOUCHER  WHERE company_id=? AND VOUCHER_TYPE_ID=? AND CREATE_TIME >= ? AND CREATE_TIME <= ?'
CaseMessage=ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 NUMBER

	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:104)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:158)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:125)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:92)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:81)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:240)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:252)
	at org.nutz.dao.impl.NutDao._count(NutDao.java:725)
	at org.nutz.dao.impl.NutDao.count(NutDao.java:687)
	at com.sys.framework.result.miniui.MIniUIGridResultMap.<init>(MIniUIGridResultMap.java:35)
	at com.fzzl.hrp.cw.zcpd.service.CwDisbursementVoucherService.queryAll(CwDisbursementVoucherService.java:129)
	at com.fzzl.hrp.cw.zcpd.controller.CwDisbursementVoucherAction.queryAll(CwDisbursementVoucherAction.java:205)
	at com.fzzl.hrp.cw.zcpd.controller.CwDisbursementVoucherAction$FM$queryAll$0775d72a02af5a2c41e18c0373b573df.invoke(CwDisbursementVoucherAction.java)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:30)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:202)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:498)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:796)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1368)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)

@qq_32b899df 我加了群,谢谢蛋总,暂时还不需要远程

Date.parse返回的不是Date对象!!!

cnd.and("CREATE_TIME", ">=", format.parse((String) pm.get("startDate")));
cnd.and("CREATE_TIME", "<=", format.parse((String) pm.get("endDate")));

@wendal @qq_32b899df 。。没注意,解决了,谢谢兽总和蛋总

添加回复
请先登陆
回到顶部