CODEWITHSUNDEEP
sql-server
user3872094
user3872094

Reputation: 3351

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.

  1. Enter the logout datetime stamp in my logout column
  2. 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.

Thanks

Upvotes: 1

Views: 34

Answers (1)

gofr1
gofr1

Reputation: 15977

You are trying to get hh:mm:ss from LOGOUT - LOGIN based on 12,8 in 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 time format.

Upvotes: 2

Related Questions