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)
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.
