Updating Error -- Using Convert Function

  • I have a varchar field called calltime that contains data that looks like this "03:34:55". I have other fields called login and logout. The calltime field is calculated by subtracting the login field from the logout field.

    So if I have login "11:45:21" and logout "11:45:55", the calltime should be "00:00:34".

    I use the following query:

    UPDATE TimeTable

    SET  CallTime = convert(varchar(20), convert(datetime, srawlogout) - convert(datetime, srawlogin),108)

    When I use this in a select statement it works, but when I try to update I get the following error:

    "Syntax error converting datetime from character string."

    Can someone please help me...What am I doing wrong???

    Thanks,

    Ninel

  • SET NOCOUNT ON

    GO

    DECLARE @myTable TABLE

    (

    myID  INT,

    srawlogin VARCHAR(25),

    srawlogout VARCHAR(25) NULL,

    CallTime  INT NULL

    )

    INSERT @myTable (myID, srawlogin) VALUES (1, '11:45:21')

    DECLARE @srawlogout VARCHAR(25)

    SET @srawlogout = '11:45:55'

    UPDATE @myTable

    SET

     srawlogout = @srawlogout,

     CallTime = DATEDIFF(SECOND, CONVERT(DATETIME, srawlogin), CONVERT(DATETIME, @srawlogout))

    WHERE

     myID = 1

    SELECT * FROM @myTable

    Regards,
    gova

  • But I would want to convert the datatypes of login and logout as DATETIME since if the data is like below it would give a negative time.

    SET NOCOUNT ON

    GO

    DECLARE @myTable TABLE

    (

    myID  INT,

    srawlogin VARCHAR(25),

    srawlogout VARCHAR(25) NULL,

    CallTime  INT NULL

    )

    INSERT @myTable (myID, srawlogin) VALUES (1, '23:45:21')

    DECLARE @srawlogout VARCHAR(25)

    SET @srawlogout = '00:45:55'

    UPDATE @myTable

    SET

     srawlogout = @srawlogout,

     CallTime = DATEDIFF(SECOND, CONVERT(DATETIME, srawlogin), CONVERT(DATETIME, @srawlogout))

    WHERE

     myID = 1

    SELECT * FROM @myTable

     

    Regards,
    gova

  • Thank you...this works, but I need the calltime to be in the same format as the login and logout...00:00:34

    How can I do this?

  • DO NOT CONVERT DATETIME TO VARCHAR!

    DO NOT CONVERT DATETIME TO VARCHAR!!!

    DO NOT CONVERT DATETIME TO VARCHAR!!!!!

    CallTime = srawlogout - srawlogin

    That's it.

    If you need inly time portion of a datetime value, use this:

    @TimeOnly = @DateTime - convert(@Datetime, FLOOR(convert(float, @Datetime)))

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply