Unable know the exception in query
I’m writing the below query in SQL Server
update time_tracker set logout = GETDATE(), totaltime = SUBSTRING(CONVERT(varchar(20), (LOGOUT - LOGIN),120),12,8) from Time_Tracker where userid = 0138039 and CONVERT(Date, LOGIN) = CONVERT(Date, GETDATE());
Basically, when the user hits a logout button I’m trying to achieve the below.
- Enter the logout datetime stamp in my logout column
- Calculate the difference between the login and logout time and update the total time column, bu checking for today’s date and the current user.
When I do this, I’m getting this exception:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
Please let me know where I’m going wrong and how can I fix this.
Also when I run the below query
Select SUBSTRING(CONVERT(varchar(20), (LOGOUT - LOGIN),120),12,8) from Time_Tracker;
It is working fine, I mean I’m getting the correct time output.
One Solution collect form web for “Unable know the exception in query”
You are trying to get
LOGOUT - LOGIN based on
SUBSTRING function. And put them into float column.
I think it is better to store differences in integer datatype in milliseconds. Or use time datatype for that column.
Try to use DATEDIFF:
Select DATEDIFF(ms,LOGIN,LOGOUT) from Time_Tracker;
That will give you the time between two dates in milliseconds.
Or ALTER your column to