时间查询时，转换函数怎么写

wendal

1楼•2479天前

传Date作为参数啊

qq_297125b4 2楼•2479天前

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

wendal

3楼•2479天前

不影响的

qq_297125b4 4楼•2479天前

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

wendal

5楼•2479天前

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

lihongjie0209

6楼•2479天前

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

wendal

7楼•2479天前

用普通的date

zhangkaidsy

8楼•2455天前

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

wendal

9楼•2455天前

用java.util.Date

zhangkaidsy

10楼•2455天前

@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

11楼•2455天前

如何不行？报错？

zhangkaidsy

12楼•2455天前

@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'

zhangkaidsy

13楼•2455天前

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

wendal

14楼•2455天前

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

zhangkaidsy

15楼•2454天前

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

wendal

16楼•2454天前

贴完整代码

来自炫酷的 NutzCN

wendal

17楼•2454天前

完整代码及完整报错信息

来自炫酷的 NutzCN

zhangkaidsy

18楼•2454天前

代码：

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)

zhangkaidsy

19楼•2454天前

@wendal

蛋蛋的忧伤(eggsblue)

20楼•2454天前

用的啥数据库?

zhangkaidsy

21楼•2454天前

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

蛋蛋的忧伤(eggsblue)

22楼•2454天前

直接< data对象不行嘛?

蛋蛋的忧伤(eggsblue)

23楼•2454天前

可否需要远程?

zhangkaidsy

24楼•2454天前

不行，试过了，还是没用

wendal

25楼•2454天前

不要format啊

来自炫酷的 NutzCN

蛋蛋的忧伤(eggsblue)

26楼•2454天前

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

蛋蛋的忧伤(eggsblue)

27楼•2454天前

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

zhangkaidsy

28楼•2454天前

不要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)

zhangkaidsy

29楼•2454天前

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

wendal

30楼•2454天前

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

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

zhangkaidsy

31楼•2454天前

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