This is an example of the string that I have to covert to a DateTime:
"29 September 2019, 8:50 PM"
Below is the code that I have written to do that conversion:
LOAD CSV WITH HEADERS FROM 'file:///myfile.csv' AS line
WITH line, SPLIT(line.Time, ',') AS dt1
WITH dt1, SPLIT(dt1, ' ') AS dt2, SPLIT (trim(dt1), ' ') AS dt3
WITH dt2, dt3, SPLIT(dt3, ':') AS dt4
WITH dt2, dt3, dt4, toInteger(dt4) AS myhour
RETURN datetime(dt2 + '-' +
WHEN 'January' THEN '01'
WHEN 'February' THEN '02'
WHEN 'March' THEN '03'
WHEN 'April' THEN '04'
WHEN 'May' THEN '05'
WHEN 'June' THEN '06'
WHEN 'July' THEN '07'
WHEN 'August' THEN '08'
WHEN 'September' THEN '09'
WHEN 'October' THEN '10'
WHEN 'November' THEN '11'
ELSE '12' END
+ '-' + dt2 + 'T' +
WHEN 'AM' AND myhour = 12 THEN toInteger(0)
WHEN 'PM' AND myhour < 12 THEN myhour + toInteger(12)
+ ':' + dt4) AS MyDateTime
The two WHEN lines in the last CASE statement do not work.
I'm trying to convert the 12 hour time to 24 hour time based on the AM or PM value.
I'm not sure how to have multiple conditions in a WHEN / CASE statement.