How to retrieve date stored in string format

[kolipakularaviturnkey (Ravi Kolipakula)]

Hi Team,

We have a requirement of report with the records from spt_identity table where column extended1 stores date in string format.

since extended1 storing in string and I wanted to generate the report using thequeryParameter filter source =“Identity”. This is not working data stored in String and Filter I am passing the as below.

Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH, -1); calendar.set(Calendar.HOUR_OF_DAY, 11); calendar.set(Calendar.MINUTE, 59); calendar.set(Calendar.SECOND, 59); Filter.ge(extended1,calendar.getTime()); return queryOptions;

extended1
19/09/2011
15/08/2002
19/03/2018
09/06/2020
26/03/2018

Regards,
Ravi.

[officialamitguptaa (Amit Gupta)]

@kolipakularaviturnkey -

Because extended1 is stored as a string in dd/MM/yyyy format, a direct Filter.ge("extended1", calendar.getTime()) will not work correctly in IdentityIQ. The comparison is done lexically (as a string) rather than as an actual date. Below approach you can use to handle this limitation.

Parse the String in Memory, Then Filter Manually

Workaround—though not always recommended—would be to load the identities first (with a broader filter or no filter on extended1), parse the date string from extended1 in Beanshell (or Java), and then discard records that do not meet the requirement.

Example (pseudocode in Beanshell):

import java.text.SimpleDateFormat;
import java.util.Date;

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
Date cutoffDate = calendar.getTime();  // whatever your calendar is

List identities = context.getObjects("sailpoint.object.Identity", yourQueryOptions);
Iterator it = identities.iterator();
while (it.hasNext()) {
    Identity idObj = (Identity) it.next();
    String dateStr = idObj.getString("extended1");
    if (dateStr != null) {
        try {
            Date extDate = sdf.parse(dateStr);
            if (extDate.before(cutoffDate)) {
               it.remove();  // remove identity from the list
            }
        } catch (Exception e) {
            // invalid date in extended1? remove or keep, your choice
            it.remove();
        }
    } else {
        it.remove();
    }
}

// Now identities list contains only those whose extended1 >= your cutoffDate
return identities;

Pros

• You can do real Date comparisons.
• No changes to the DB schema or data format needed.

Cons

• Potentially expensive: You’re loading more rows than you need and filtering in-memory.
• Doesn’t leverage DB filtering (not ideal for large Identity populations).

[mkumariaas (M Kumar)]

@kolipakularaviturnkey As the data is in string and you are passing long value so it will not work.
There is 2 ways:-

1. You can create a method and get string value Date in List which you want and then use filter.in(“Extended1”,list); this will be huge in case if you have more days to be calculated if you have upto 10 or twenty then u can use this.
2. If you have dba team you can ask them that we need the query where we have date between need to get output and compare those with string format. You can search in AI tool those query and u can use Context.search("sql : "+sql,null,null)
   rather context.search(identity.class
   so it will help you to find all the required data.