Convert Timestamp into date-time

[Roost1969]

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]

Try this. Adjust the table and column name accordingly. This formula assumes that your original column is in text format.

DateTime_Converted =

VAR textDateTime = datetimesample[datetime]

VAR cleanedDateTime = SUBSTITUTE(SUBSTITUTE(textDateTime, "@ ", ""), ",", "")

VAR datePart = LEFT(cleanedDateTime, LEN(cleanedDateTime) - 12)

VAR timePartWithMilliseconds = RIGHT(cleanedDateTime, 12)

VAR timePart = LEFT(timePartWithMilliseconds, 8)  // Extracting only the HH:MM:SS part

RETURN

    DATEVALUE(datePart) + TIMEVALUE(timePart)

[Roost1969]

Unfortunately this code doesn't work either, maybe I can send you a PBix?

[amustafa]

Sure, that will help

[Roost1969]

thank you, de Time convert part works fine, but the Date part has a issue.  It says"Connot convert value 'Oct 31 2023' of type text to type Date.

[amustafa]

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)

[Roost1969]

After a import of a new dataset, the dataconvert works.

Thanks you for your help.