Get date from string Kusto

Question

Can someone tell me why this does not work?

let x = "July, 2021";
let calculatedTime = replace_string(x,","," 01");
print(todatetime(calculatedTime))

while this works?

let y = todatetime("July 01 2021");
print y

How to get 1st day of the month from a string like "July, 2021"?

Answer 1

The reason that you are seeing this discrepancy is that the constant value in your second query is being parsed by the query string parser using .Net library that supports this format, while the first expression is being evaluated by the query engine which is using different libraries, these libraries supports the datetime formats specified here.

Here is workaround for this issue, most likely this can be written simpler:

let GetMonthNumber = view(Month:string){
case(
    Month=="January", "01",
    Month=="February", "02",
    Month=="March", "03",
    Month=="April", "04",
    Month=="May", "05",
    Month=="June", "06",
    Month=="July", "07",
    Month=="August", "08",
    Month=="September", "09",
    Month=="October", "10",
    Month=="November", "11",
    Month=="December", "12", 
    "-1" // default case is an error
)};
let x = "July, 2021";
let dateparts = split(x, ",");
let calculatedTime = strcat(replace_string(tostring(dateparts[1]), " ", ""),"-", GetMonthNumber(tostring(dateparts[0])), "-01");
print todatetime(calculatedTime)

print_0
2021-07-01 00:00:00.0000000