获取日期时间差异的异常 [英] Getting an exception with datetime diff

问题描述

我的网页上有2个按钮.当我按下第一个按钮时，系统时间将在sql表的starttime中更新，而当我单击停止时，必须更新结束时间，并且应该在另一个表中更新两者之间的差异.我正在使用以下查询.

I've 2 buttons on my webpage. And when I hit the first button, the system time gets updated in starttime of my sql table, and when I hit a stop, The end time has to be updated and the difference between both should be updated in anothere table. I've using the below queries.

更新结束时间并更新总时间.

To update the end time and to update the total time.

update breakstable set endtime = ?, TotalBreakTime = (? - StartTime)  where userid = ? 
and endtime is NULL

这里的前2个?指的是发生的按钮单击，而另一个?表示从会话中捕获登录的userId.

here first 2 ?s refers to a button click that happens and another ? for the logged in userId getting captured from session.

使用totalbreaktime的总和更新另一个表.

update another table with the sum of the totalbreaktime.

MERGE Time_Tracker as target using (SELECT USERID, CONVERT(NUMERIC(10,2),
SUM(DATEDIFF(Second, '19000101', TotalBreakTime))/60.0) as ColumnWithBreaksCount FROM 
BreaksTable where CONVERT(Date, StartTime) = CONVERT(Date, GETDATE()) GROUP BY USERID) 
as source ON target.USERID = source.USERID WHEN MATCHED THEN UPDATE 
SET BREAKS = source.ColumnWithBreaksCount;"

问题:

我开始计时，休息一下，一个半小时后返回，然后我按下了停止按钮.而不是更新表，而是给了我下面的异常.

I start my time, go on a break and return after an hour and half and i hit the stop button. Instead of updating the table it is giving me the below Exception.

com.microsoft.sqlserver.jdbc.SQLServerException:datediff 函数导致溢出.分开的日期部分的数量 两个日期/时间实例太大.尝试使用datediff少 精确的日期部分.

com.microsoft.sqlserver.jdbc.SQLServerException: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

和JDBC异常(对于Java专家)如下.

and the JDBC Exception (For Java guys) is as below.

com.microsoft.sqlserver.jdbc.SQLServerException:datediff函数 导致溢出.分隔两个的日期部分的数量 日期/时间实例太大.尝试使用datediff少 精确的日期部分.在 com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196) 在 com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454) 在 com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388) 在 com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement $ PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338) 在 com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026) 在 com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416) 在 com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185) 在 com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160) 在 com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306) 在 org.DAO.UpdateEndTimeDAO.UpdateEndTimeDetails(UpdateEndTimeDAO.java:48) 在org.servlet.UpdateEndTime.doPost(UpdateEndTime.java:38)处 javax.servlet.http.HttpServlet.service(HttpServlet.java:648)在 javax.servlet.http.HttpServlet.service(HttpServlet.java:729)在 org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291) 在 org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 在 org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 在 org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) 在 org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 在Filter.MyFilter.doFilter(MyFilter.java:58)在 org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) 在 org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 在 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217) 在 org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) 在 org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) 在 org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142) 在 org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) 在 org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) 在 org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) 在 org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518) 在 org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091) 在 org.apache.coyote.AbstractProtocol $ AbstractConnectionHandler.process(AbstractProtocol.java:673) 在 org.apache.tomcat.util.net.NioEndpoint $ SocketProcessor.doRun(NioEndpoint.java:1500) 在 org.apache.tomcat.util.net.NioEndpoint $ SocketProcessor.run(NioEndpoint.java:1456) 在java.util.concurrent.ThreadPoolExecutor.runWorker(未知来源) 在java.util.concurrent.ThreadPoolExecutor $ Worker.run(未知来源) 在 org.apache.tomcat.util.threads.TaskThread $ WrappingRunnable.run(TaskThread.java:61) 在java.lang.Thread.run(未知来源)

com.microsoft.sqlserver.jdbc.SQLServerException: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306) at org.DAO.UpdateEndTimeDAO.UpdateEndTimeDetails(UpdateEndTimeDAO.java:48) at org.servlet.UpdateEndTime.doPost(UpdateEndTime.java:38) at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at Filter.MyFilter.doFilter(MyFilter.java:58) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456) 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)

该异常指向我的问题中声明的第二个查询.

And the exception points to the second query declared in my question.

如果时间较短，例如说2分钟或3分钟，那么它正在更新，没有任何问题.

If the time is less, say like 2 min or 3 mins, it is updating without any issue.

请问我要去哪里错了，我该如何解决.

please where am I going wrong and how can I fix this.

我的休息"表如下所示

这应该是单击按钮时捕获的结束时间，而不是空值

Instead of null, this should be the end time captured on button click

我的时间跟踪器如下所示.

And my Time tracker looks like below.

而不是上面的屏幕截图中的4.97，它应该是我的第一个屏幕截图中的totalbreaktime的总和.

instead of 4.97 in above screenshot it should be the sum of the totalbreaktime from my first screenshot.

谢谢

推荐答案

尝试一下:

MERGE Time_Tracker as target 
   using 
    (SELECT USERID, 
        Sum(cast(cast(TotalBreakTime as float) 
              * 86400 as bigint)) ColumnWithBreaksCount
     FROM BreaksTable b 
     Where datediff(day, StartTime, GETDATE()) = 0
     GROUP BY USERID) source 
ON target.USERID = source.USERID 
WHEN MATCHED THEN UPDATE 
SET BREAKS = source.ColumnWithBreaksCount;" 

这篇关于获取日期时间差异的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！