How to convert String into Date and then compare two Dates

[aditsss]

Hi Everyone,

I have one requirement

I have one CSV file JOB_MDJX_CS_EXTR_STATS_2.csv from where I am getting Rundate2 field in below format:

210510

210507

so  I have added  variable 20 and created a new field as Run_date1  like this

I have used below query

|inputlookup JOB_MDJX_CS_EXTR_STATS_2.csv|eval y=20|eval Run_date1= y."".RUNDATE2|stats count by Run_date1

My result is this

Run_date1                              count

20210429                                2
20210430                               21
20210501                              15
20210503                              29
20210504                              26
20210507                               5

20210510                               2

Now I have convert Run_date1 into date format and also created nowdate column in date format as shown in below query and then compare them as shown in below query:

|inputlookup JOB_MDJX_CS_EXTR_STATS_2.csv|where Environment="E1"|where JOBFLOW_ID LIKE "%%"|eval y=20|eval Run_date1= y."".RUNDATE2|eval Run_Date=strftime(strptime(Run_date1,"%Y%m%d"),"%d/%b/%Y")|eval nowdate=strftime(relative_time(now(), "-7d@d" ), "%d/%b/%Y")|where Run_Date>=nowdate|table nowdate Run_Date

But the issue I am facing is I am getting the result like this:

If the nowdate is 7thMay as per the query it should show only the result equal and greater then 7thMay 

but why it is showing 30thApril and 29thApril.It should show only 7thMay and 10th May.

Is my date format for Rundate and nowdate not correct in query.

Can someone please guide me on this

nowdate              Run_Date
07/May/2021  10/May/2021
07/May/2021 10/May/2021
07/May/2021 07/May/2021
07/May/2021 07/May/2021
07/May/2021 07/May/2021
07/May/2021 07/May/2021
07/May/2021 07/May/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 30/Apr/2021
07/May/2021 29/Apr/2021
07/May/2021 29/Apr/2021

[ITWhisperer · Accepted solution]

To compare dates you need to leave them as numbers.

|inputlookup JOB_MDJX_CS_EXTR_STATS_2.csv
|where Environment="E1"
|where JOBFLOW_ID LIKE "%%"
|eval Run_date1="20".RUNDATE2
|eval Run_Date=strptime(Run_date1,"%Y%m%d")
|eval nowdate=relative_time(now(), "-7d@d" )
|where Run_Date>=nowdate
|table nowdate Run_Date
|fieldformat nowdate=strftime(nowdate,"%d/%b/%Y")
|fieldformat Run_Date=strftime(Run_Date,"%d/%b/%Y")

[aditsss]

@ITWhisperer 

You are best as always. Thank you for the quick solution.