Hi,
How to convert this SQL statement to SPL pls
select DateDiff(day, ga.Initial_L1_Decision_Date, Close_date) as [Days Since Initial L1 Decision] from table
If both the dates are in epoch format, you can do like this
..| eval "Days Since Initial L1 Decision"=round(abs('Close_date'-'ga.Initial_L1_Decision_Date')/86400)
If they're not epoch, you need to convert them to epoch for calculation, like this (update the timeformat in strptime according to yours)
..| eval "Days Since Initial L1 Decision"=round(abs(strptime('Close_date',"%Y-%m-%d %H:%M:%S")-strptime('ga.Initial_L1_Decision_Date',"%Y-%m-%d %H:%M:%S"))/86400)
Hi raghu0463,
I'm not sure of one thing: in your select it seems that there are three fields in Diff (day, ga.Initial_L1_Decision_Date, Close_date), are really three or not?
if you have two dates, try something like this:
your_search
| eval "Days Since Initial L1 Decision"=(strptime(ga.Initial_L1_Decision_Date,"%m/%d/%Y %H:%M:%S")-strptime(Close_date,"%m/%d/%Y %H:%M:%S"))/3600/24
| table your_fields "Days Since Initial L1 Decision"
I don't know time format of your dates, if they are wrong, modify the ones I used.
Bye.
Giuseppe
If both the dates are in epoch format, you can do like this
..| eval "Days Since Initial L1 Decision"=round(abs('Close_date'-'ga.Initial_L1_Decision_Date')/86400)
If they're not epoch, you need to convert them to epoch for calculation, like this (update the timeformat in strptime according to yours)
..| eval "Days Since Initial L1 Decision"=round(abs(strptime('Close_date',"%Y-%m-%d %H:%M:%S")-strptime('ga.Initial_L1_Decision_Date',"%Y-%m-%d %H:%M:%S"))/86400)
is that strptime is for single field or do i need to apply for both together ?
You need to apply strptime for each field which you want to convert from string to date/epoch.