Date Conversion

[raghu0463]

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

[gcusello]

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

[somesoni2 · Accepted solution]

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)

[raghu0463]

is that strptime is for single field or do i need to apply for both together ?

[somesoni2]

You need to apply strptime for each field which you want to convert from string to date/epoch.