Forum Discussion

Roost1969's avatar
Roost1969
Frequent Visitor
2 years ago

Convert Timestamp into date-time

Goodday,   I have a CSV file withe a data-time field witch has the format (Jan 12, 2024 @ 19:39:03.793). Is there a way to convert this format to a DD-MM-YYYY, HH:MM:SS  with a dax code?
amustafa's avatar
amustafa
Solution Sage
2 years ago

Well, you have two different formats of date in your data then. here's the updated DAX to handle both formats.

 

DateTime_Converted =
VAR textDateTime = datetimesample[datetime]
VAR containsTime = CONTAINSSTRING(textDateTime, "@")
VAR cleanedDateTime = SUBSTITUTE(SUBSTITUTE(textDateTime, "@ ", ""), ",", "")
VAR datePart = IF(containsTime, LEFT(cleanedDateTime, LEN(cleanedDateTime) - 12), cleanedDateTime)
VAR timePartWithMilliseconds = IF(containsTime, RIGHT(cleanedDateTime, 12), "00:00:00")
VAR timePart = IF(containsTime, LEFT(timePartWithMilliseconds, 8), timePartWithMilliseconds) // Extracting only the HH:MM:SS part or default to 00:00:00 if no time part
RETURN
DATEVALUE(datePart) + TIMEVALUE(timePart)