I was trying a few options on date & time formatting but could not find a solution. The problem is the following:
I have quarterly data, the quarter identifier is in the column Year_quarter in the format yyyyq (alternatively yyyyqq), e.g. 20194 means “2019 quarter 4”.
This column Year_quarter is currently formatted as string.
Now, I would want KNIME to acknowledge the quarterly date format so that I can perform time shifts.
So far, I tried “String to Date&Time” but it failed: --> “Execute failed: Failed to parse date in row 'Row0_Row168_Row0_Row168:…”
Don’t think you can get Local Date type from 2019xx where xx determines quarter and you need it in order to use Date&Time Shift node.
What you can do is build logic to transform 20194 to represent first day in that quarter (so 2019-10-01) and then use String to Date&Time node to get your desired type. Use Rule Engine with following syntax
$Year_quarter$ LIKE "*1" => "-1-1"
$Year_quarter$ LIKE "*2" => "-4-1"
$Year_quarter$ LIKE "*3" => "-7-1"
$Year_quarter$ LIKE "*4" => "-10-1"
followed by this expression in String Manipulation node
it is not a workaround cause you can not parse format yyyy-qq into Date&Time in Java (and doesn’t make much sense either). You use qq placeholder when you go from Date&Time to String (formatting). This makes sense as it can go from yyyy-mm-dd (Date&Time) to yyyy-qq (String).