topic Re: Convert Timestamp into date-time in DAX Commands and Tips

Convert Timestamp into date-time

Roost1969 — Tue, 16 Jan 2024 14:48:43 GMT

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?

Re: Convert Timestamp into date-time

amustafa — Tue, 16 Jan 2024 15:52:54 GMT

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)

Re: Convert Timestamp into date-time

Roost1969 — Wed, 17 Jan 2024 09:37:37 GMT

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.

Re: Convert Timestamp into date-time

amustafa — Wed, 17 Jan 2024 11:08:09 GMT

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)

Re: Convert Timestamp into date-time

Roost1969 — Thu, 18 Jan 2024 06:50:27 GMT

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

Re: Convert Timestamp into date-time

amustafa — Thu, 18 Jan 2024 09:54:16 GMT

Sure, that will help

Re: Convert Timestamp into date-time

Roost1969 — Fri, 19 Jan 2024 08:10:38 GMT

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

Thanks you for your help.