Forum Discussion

TanLC's avatar
TanLC
Frequent Visitor
1 year ago
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
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
rajasaadk_98
Helper I
1 year ago

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;