Kusto convert string to date

Question

Writing a kusto query I have a date time format of yyyyMMdd HHmmss.SSS eg: 20230726 121033.000. When using the todatetime I am unable to convert the format to date/time.

project todatetime(['TimeStamp_s']) does not return any result.

Answer 1

yyyyMMdd HHmmss.SSS is not supported format. Refer this document for the supported formats in Kusto datetime() function.

let MyTable = datatable(TimeStamp_s:string, OtherColumn1:string, OtherColumn2:string)
[
    '20230726 121033.000', 'Value1', 'Value2',
    '20230727 093015.123', 'Value3', 'Value4',
    '20230728 184500.999', 'Value5', 'Value6'
];
MyTable
| project todatetime(strcat(substring(['TimeStamp_s'],0,4),'-',substring(['TimeStamp_s'],4,2),'-',substring(['TimeStamp_s'],6,2),'T',substring(['TimeStamp_s'],9,2),':',substring(['TimeStamp_s'],11,2),':',substring(['TimeStamp_s'],13,6)))

The substring() function is used to extract the year, month, day, hour, minute, and second components from the TimeStamp_s string, and the strcat() function is used to concatenate them into a string. This way, format is changed to ISO 8601 datetime format and given as input to todatetime() function.