Found it!
if firstExec then {
Threshold_DT = date("2017-07-01","CCYY-MM-DD")
}
Match_Before = dateSubtract(START_DATE, Threshold_DT) < 0
emit * where Match_Before
Found it!
if firstExec then {
Threshold_DT = date("2017-07-01","CCYY-MM-DD")
}
Match_Before = dateSubtract(START_DATE, Threshold_DT) < 0
emit * where Match_Before
I'm glad you found a solution.
You can convert NULL string values or empty/whitespace strings to a NULL date type using the equivalent of this in a Filter node:
Note that a NULL value is effectively treated as the lowest possible value so when looking for dates before a threshold the NULL values would also be included in the output.Code:if isNull(DateStr) or trim(DateStr) == "" then { START_DATE = date(null) } else { START_DATE = date(DateStr,"CCYY-MM-DD") } emit START_DATE
If you wanted to exclude the NULL records you could modify your date filter to have the following code:
Code:if firstExec then { Threshold_DT = date("2017-07-01","CCYY-MM-DD") } Match_Before = dateSubtract(START_DATE, Threshold_DT) < 0 Match_NotNull = isNotNull(START_DATE) emit * where Match_Before and Match_NotNull
Very useful, thanks a lot!
Another problem!
How can I keep the date only from string with format: 7/6/2017 11:21:50 and convert it to: "CCYY-MM-DD" ?
the day could be with 2 digits as well: 17/6/2017 11:21:50. I need to cover both cases and make the conversion...
Somehow this worked and autoconverted the date:
a=timestamp(date('Date of call'.left(9),"D/M/CCYY"),time('Date of call'.right(8),"HH:MM:SS"))
emit *,a
If the date element in the datetime string can have a one-digit or two-digit day component (and obviously the month component will also have both one and two digits) the most reliable way of separating the date element from the time element will be to split the string using the space character as the delimiter. As you have found out, the date operator's format string "D/M/CCYY" allows the day and month components to have one or two digits. You can use the following code to convert the string to a datetime:
Code:#Split datetime string on space character Elements = 'Date of call'.split(" ") a= timestamp(date(Elements[0],"D/M/CCYY"), time(Elements[1],"HH:MM:SS")) emit *, a