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.

     

3 Replies

  • 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;


  • TanLC's avatar
    TanLC
    Frequent Visitor
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      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.