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

Basically, when the user hits a logout button I’m trying to achieve the below.

  • Deleting duplicate record from table - SQL query
  • How to display Date in DD/MM/YYYY H:MM AM/PM format in SQL Server
  • sqlSave: Mapping dataframe timestamps to SQL Server timestamps
  • meaning of the values of sent_status on msdb.dbo.sysmail_mailitems
  • Cannot insert the value NULL into column 'NullBuster'
  • Unable to set a condition in SQL Server 2008 R2
    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

        SUBSTRING(CONVERT(varchar(20), (LOGOUT - LOGIN),120),12,8) 

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.