topic Re: Covert Text type to DateTime type in Developer

Covert Text type to DateTime type

TanLC — Mon, 07 Oct 2024 03:56:58 GMT

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

Thank you.

Regards,

Re: Covert Text type to DateTime type

rajasaadk_98 — Mon, 07 Oct 2024 05:52:34 GMT

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;

Re: Covert Text type to DateTime type

TanLC — Mon, 07 Oct 2024 06:51:51 GMT

@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,

Re: Covert Text type to DateTime type

Anonymous — Tue, 08 Oct 2024 07:14:06 GMT

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.