Update Function

  • Hello everyone,

    I need your help.

    I have a “datetime” column (data type is Varchar(64) )with the following content: Sun Sep 29 2024 09:28:55 GMT+0000 (Coordinated Universal Time)

    I would like to update the column so that I only get the time in the format HH:MM:SS. I have tried many solutions, but none of them worked.

    There are over 17000 rows in the column in this format and I want to change them all.

    I hope you can help me.

    Thank you very much

  • Like this, perhaps?

    DROP TABLE IF EXISTS #SomeData;

    CREATE TABLE #SomeData
    (
    SomeDate VARCHAR(64)
    );

    INSERT #SomeData
    (
    SomeDate
    )
    VALUES
    ('Sun Sep 29 2024 09:28:55 GMT+0000 (Coordinated Universal Time)')
    ,('string not found');

    SELECT *
    FROM #SomeData sd;

    UPDATE sd
    SET sd.SomeDate = SUBSTRING (sd.SomeDate, pos.StartPos, 8)
    FROM #SomeData sd
    CROSS APPLY
    (
    SELECT StartPos = PATINDEX ('%[0-9][0-9]:[0-9][0-9]:[0-9][0-9]%', sd.SomeDate)
    ) pos
    WHERE pos.StartPos > 0;

    SELECT *
    FROM #SomeData sd;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Dates, datetimes and times are stored in a special binary format in SQL Server.  You don't change the column format, you just change the display format.

    SELECT CONVERT(varchar(8), datetime_column, 8)

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • If you really want to remove the date from the column, then change the column type to "time".

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • That isn't a valid date format. What you likely need to do is something like what Phil has, if that's correct. If you don't need the dates, then use that to change to just times.

     

    Date and time styles: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#date-and-time-styles

     

  • fk.da wrote:

    Hello everyone,

    I need your help. I have a “datetime” column (data type is Varchar(64) )with the following content: Sun Sep 29 2024 09:28:55 GMT+0000 (Coordinated Universal Time)

    I would like to update the column so that I only get the time in the format HH:MM:SS. I have tried many solutions, but none of them worked.

    Do all of the rows have that exact same format of ddd mmm dd yyyy hh:mi:ss GMT+0000 (Coordinated Universal Time)

    If so, then just use SUBSTRING(yourcolumn,17,8) to isolate the data you want to keep, which is just the time.

    I would then dump that into a new TIME column instead of overwriting the original data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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