Covert Text type to DateTime type

[TanLC]

Hi,  kindly advice on how to convert "24052024044827" to "24/05/2024 04:48:27 AM" using SQL Syntax.

Thank you.

Regards,

LC

[TanLC]

@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

[Anonymous · Accepted solution]

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.

[rajasaadk_98]

To convert the string "24052024044827" into the desired date and time format "24/05/2024 04:48:27 AM" in SQL (within Power BI or SQL Server), you can follow these steps:

SQL Syntax:

You will need to break the string into parts and then use CONVERT or FORMAT functions to assemble it as a datetime format.

Here’s a solution that works in SQL Server:
SELECT FORMAT(CONVERT(DATETIME,
SUBSTRING('24052024044827', 1, 2) + '/' + -- Day
SUBSTRING('24052024044827', 3, 2) + '/' + -- Month
SUBSTRING('24052024044827', 5, 4) + ' ' + -- Year
SUBSTRING('24052024044827', 9, 2) + ':' + -- Hour
SUBSTRING('24052024044827', 11, 2) + ':' + -- Minute
SUBSTRING('24052024044827', 13, 2) -- Second
, 103), 'dd/MM/yyyy hh:mm:ss tt') AS FormattedDate;