Convert String to Datetime (retaining fractional seconds)

Version 2

    Description:

    Tableau's inbuilt conversion function for converting a string representation of a date and time to a datetime field is DATETIME(). This works fine as long as the precision of the time portion of the field does not go below the second. But if the time component includes fractions of a second, the DATETIME() function returns NULL.

     

    The way around this is to use string functions to break out the fractional second part of the input string, convert that to a floating point number representing the fraction of a second and then add the result to the datetime value obtained by converting the string date and time with the fractional seconds removed.

    Example Calculation:

    The exact format of the required calculation depends on the format of the input string. Assuming that the string ends with the fractional seconds and has the date and time in a format supported by Tableau's DATETIME() function, the required calculation is as follows.

     

    DATETIME(LEFT([Datetime (String)], FIND([Datetime (String)], ".") - 1)) +

        (FLOAT(MID([Datetime (String)], FIND([Datetime (String)], "."))) / (24*60*60))


    Comments:

    For example, consider the following date and time string:

     

    "31-Jan-2011 15:35:22.270"

     

    The calculation would evaluate this as follows:

     

    DATETIME("31-Jan-2011 15:35:22") + (FLOAT(".270") / 86400)