Date comparison with if statements

[mcamilleri]

I need to use an if statement to set the dates in startDateFrom and startDateTo if not specified in the selectedStartDateFrom and selectedStartDateTo variables.

I then want to use startDateFrom and startDateTo to filter for entries with Experiment_Instance_Start_Date between startDateFrom and startDateTo.

The date comparison works fine, it's only when I add the if statements that it stops working. Any ideas what I'm doing wrong here?

| eval compare=strptime(Experiment_Instance_Start_Date,"%m/%d/%Y") 
| where compare>=strptime(startDateFrom,"%m/%d/%Y")
| eval compare=strptime(startDateTo,"%m/%d/%Y")
| where compare>=strptime(Experiment_Instance_Start_Date,"%m/%d/%Y")
| eval startDateFrom=if("$selectedStartDateFrom$"="", "01/01/1970", "$selectedStartDateFrom$")
| eval startDateTo=if("$selectedStartDateTo$"="", "01/01/2100", "$selectedStartDateTo$")

[mcamilleri · Accepted solution]

As aholzer said in the comments above, "$selectedStartDateFrom$"="" is wrong, so I changed that to len("$selectedStartDateFrom$")>0. I also needed to move the if statements to before the date comparison for the startDateFrom and startDateTo to be available. Also start date of 01/01/1970 returns no results, so I changed it to a later date.

| eval startDateFrom=if(len("$selectedStartDateFrom$")>0, "$selectedStartDateFrom$", "01/01/2000")
| eval startDateTo=if(len("$selectedStartDateTo$")>0, "$selectedStartDateTo$", "01/01/2099")
| eval compare=strptime(Experiment_Instance_Start_Date,"%m/%d/%Y") 
| where compare>=strptime(startDateFrom,"%m/%d/%Y")
| eval compare=strptime(startDateTo,"%m/%d/%Y")
| where compare>=strptime(Experiment_Instance_Start_Date,"%m/%d/%Y")

[mcamilleri]

great, that helped a lot. I'm using len now and I also had to move the if statements before the date comparison for the startDateFrom and startDateTo to be available

[aholzer]

Looks like you are doing a string comparison rather than a date comparison. The double quotes around your $selectedStartDateFrom$ convert it to a string.

In string comparisons you need a == comparison rather than an = comparison: "$selectedStartDateFrom$"==""

If this doesn't work, you could also do something like: len("$selectedStartDateFrom$")>0

hope this helps

[mcamilleri]

They're tokens provided from a form

[sowings]

Are selectedStartDateFrom and selectedStartDateTo fields from your data, or tokens, as provided from a form? The syntax of your if will vary depending upon the answer.