Azure KQL: convert date in string format "20210820122039" to datetime in azure KQL

Question

I have a column in azure application insights which is string where date is stored as string in the format 20210820122039 (yyyymmddhhmmss) I want to create new column which will be copy of the existing column by converting all values to date format (default format is fine). Can some one help me with API which helps to do this in Azure KQL.

Below is some information about data and query.

Json Data stored in custom dimensions. optional header is extracted from custom dimensions looks something like this. {"UTCDate":"2021-08-20T08:41:28.0260000Z",,"Date":"20210820104125","isPresent":"False"}

I want Date value to be converted to datetime data type with default date format say yyyy-mm-dd hh:mm:ss

Below is my KQL

traces
| extend optionalHeaders=todynamic(tostring(customDimensions.OptionalHeaders))
| extend DateMeta=todatetime(optionalHeaders.Date_metaData)

This is resulting in empty column also todatetime does not support passing format argument like this todatetime(optionalHeaders.Date_metaData,'yyyy-mm-dd hh:mm:ss')

Answer 1

FWIW you can also use this: | parse kind=regex flags=Uis Time with day "/" month "/" year " " hour ":" minute ":" second "." millisecond " " timezone "$" as an example to parse a timestring in the format 01/01/1970 00:01:00.000 +0100

Answer 2

as this is an unsupported datetime format (see: doc), you'll need to parse the datetime parts out of the value, then use make_datetime() to construct the datetime value:

print d = dynamic({"UTCDate":"2021-08-20T08:41:28.0260000Z","Date":"20210820104125","isPresent":"False"})
| project dt_str = tostring(d.Date)
| project result = make_datetime(
    toint(substring(dt_str, 0, 4)),
    toint(substring(dt_str, 4, 2)),
    toint(substring(dt_str, 6, 2)),
    toint(substring(dt_str, 8, 2)),
    toint(substring(dt_str, 10, 2)),
    toint(substring(dt_str, 12, 2)))

result
2021-08-20 10:41:25.0000000

Answer 3

let dt = datetime(20210820122039);
print 
dt