October 30, 2024 at 1:32 pm
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 2:22 pm
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
October 30, 2024 at 4:11 pm
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".
October 30, 2024 at 4:13 pm
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".
October 30, 2024 at 10:23 pm
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
October 30, 2024 at 11:55 pm
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
Change is inevitable... Change for the better is not.
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