Forum Discussion
Solved
Covert Text type to DateTime type
Hi, kindly advice on how to convert "24052024044827" to "24/05/2024 04:48:27 AM" using SQL Syntax. Thank you. Regards, LC
Hi TanLC ,
Here I test with rajasaadk_98's code, you just need to use your column name to replace the string and add the table from.
USE [Test] GO SELECT [Category], FORMAT(CONVERT(DATETIME, SUBSTRING(DateTime, 1, 2) + '/' + -- Day SUBSTRING(DateTime, 3, 2) + '/' + -- Month SUBSTRING(DateTime, 5, 4) + ' ' + -- Year SUBSTRING(DateTime, 9, 2) + ':' + -- Hour SUBSTRING(DateTime, 11, 2) + ':' + -- Minute SUBSTRING(DateTime, 13, 2) -- Second , 103), 'dd/MM/yyyy hh:mm:ss tt') AS FormattedDate FROM [dbo].[Table_1] ;Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rajasaadk_98 thanks for the syntax given. What if in the same column, there are actually blank records, SUBSTRING will not be able to work...
Regards,
LC
Hi TanLC ,
Here I test with rajasaadk_98's code, you just need to use your column name to replace the string and add the table from.
USE [Test]
GO
SELECT [Category],
FORMAT(CONVERT(DATETIME,
SUBSTRING(DateTime, 1, 2) + '/' + -- Day
SUBSTRING(DateTime, 3, 2) + '/' + -- Month
SUBSTRING(DateTime, 5, 4) + ' ' + -- Year
SUBSTRING(DateTime, 9, 2) + ':' + -- Hour
SUBSTRING(DateTime, 11, 2) + ':' + -- Minute
SUBSTRING(DateTime, 13, 2) -- Second
, 103), 'dd/MM/yyyy hh:mm:ss tt') AS FormattedDate
FROM [dbo].[Table_1]
;
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.