Getting an exception with datetime diff

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

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

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;"

problem:

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: 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.

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

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.

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.

My Breaks table looks like below

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

And my Time tracker looks like below.

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

Thanks

回答1:

This is because DATETIME functions like DATEDIFF, DATEADD etc. accept and returns integer (INT) data type. Limit of the data type is 2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

So if you add -2147483648 to TotalBreakTime. It returns "1948-08-08 14:01:46.000"

SELECT DATEADD(SS,CAST(-2147483648 AS BIGINT),'2016-08-26 17:15:54.000');

And more the the limit of datatype returns ERROR.

Arithmetic overflow error converting expression to data type int.

Because date difference in second is too large (out of limit of datatype) between the dates in the query. It returns the error.

SELECT CONVERT(NUMERIC(10,2),
SUM(CAST(DATEDIFF(Second, '19000101', '2016-08-26 17:15:54.000') AS BIGINT))/60.0)

I would suggest to discuss logic with you team resolve the issue accordingly.

Thanks

回答2:

try this:

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;"