-1

I have string data like '20230823143306'. Is there any way to convert it to a datetime format, like '08/23/2023 02:33:06 PM', using SQL?

I've heard that it might be possible to perform this conversion using Apex, but so far, no one is capable of doing Apex. I'd like to figure out if it's possible within Marketing Cloud (MC)

1 Answer 1

2

Based on what you provided, the string data is having the datetime format like this yyyyMMddHHmmss (20230823143306) so I would convert it to a SQL datetime data type then format it to "dd/MM/yyyy hh:mm:ss tt" to achieve the outcome.

E.g. You have the DE

Date_Num_Val
20230823143306
20230823074010

In the SQL below, I extracted each date part (year, month, day, hour,...) to concentrate strings to "YYYY-MM-DD hh:mm:ss" then convert it to the expected format:

SELECT
FORMAT(
CAST( CONCAT(
LEFT(Date_Num_Val, 4) , '-',
RIGHT(LEFT(Date_Num_Val, 6) , 2 ) , '-',
RIGHT(LEFT(Date_Num_Val, 8) , 2 ) , ' ',
RIGHT(LEFT(Date_Num_Val, 10) , 2 ), ':',
RIGHT(LEFT(Date_Num_Val, 12) , 2 ), ':',
RIGHT(LEFT(Date_Num_Val, 14) , 2 )
) AS DATETIME), 'dd/MM/yyyy hh:mm:ss tt') AS formatedDate
FROM DE

Output:

formatedDate
23/08/2023 02:33:06 PM
23/08/2023 07:40:10 AM

Try it yourself here: https://dbfiddle.uk/5qX8DDsA

Note: It depends on the use case you are facing. For example, if you want to use the datetime format in an email, then you may consider using AMPscript.

1
  • Thank you so much! I solved the problem. Aug 24, 2023 at 1:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .