Update Function

Question

Post reply

Update Function

fk.da

SSC Enthusiast

Points: 180
More actions
October 30, 2024 at 1:32 pm

#4475422

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

October 30, 2024 at 11:59 pm

This was removed by the editor as SPAM
October 31, 2024 at 12:00 am

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

Phil Parkin SSC Guru Points: 246940 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 101073 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 101073 More actions · Answer 3

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

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 5

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)