I want to format the datetime on Azure Log this is the date time format

DATETIME = 01/Sep/2022:04:48:11 0000

I tried to split and get 01/Sep/2022 but it wont convert

SampleLog_CL 
| extend raw = parse_json(RawData).log
| parse raw with DATETIME
| extend dt = split(DATETIME, ':')
| project DATE=format_datetime(todatetime(dt[0]), 'yyyy-MM-dd')

when I try to put the actual value 01/Sep/2022 it can convert

SampleLog_CL 
| extend raw = parse_json(RawData).log
| parse raw with DATETIME
| extend dt = split(DATETIME, ':')
| project DATE=format_datetime(todatetime("01/Sep/2022"), 'yyyy-MM-dd')

How can I convert it?

CodePudding user response：

Well...

// Data sample generation. Not part of the solution
let SampleLog_CL = datatable(RawData:dynamic)
[
    dynamic({"log":"01/Sep/2022:04:48:11  0000"})
];
// Solution starts here
let months = dynamic({"Jan":01, "Feb":"02", "Mar":"03", "Apr":"04", "May":"05", "Jun":"06", "Jul":"07", "Aug":"08", "Sep":"09", "Oct":"10", "Nov":"11", "Dec":"12"});
SampleLog_CL 
| extend raw = parse_json(RawData).log
| parse raw with d "/" M "/" y ":" h ":" m ":" s " " o
| extend Timestamp = todatetime(strcat(y, "-", months[M], "-", d, " ", h, ":", m, ":", s, o))