Date Comparision Issue in IIQ

IdentityIQ (IIQ) IIQ Discussion and Questions
1

Which IIQ version are you inquiring about?

Version 8.3

Share all details related to your problem, including any error messages you may have received.

Hello Everyone,

For identities in IIQ we have multiple applications for which accounts are created. There is two specific applications NDI and FieldGlass. these two are having different date formats. The requirement is to create a query to select SSO, FieldGlass_end_date, NDI_end_date where FieldGlass_end_date <> NDI_end_date. For this I created below query which is working fine for single SSO
In Fieldglass End_Date format is
In NDI it is called hreffectiveenddate and the format is

Both dates are same Nov-4-2024, but their format is different. So after parsing, I need to convert both dates in same format so that I can compare them.
Here is the query which is working fine for single sso.

SELECT * FROM (
    SELECT Display_Name_FG,Display_Name_NDI, IDB, 
        TO_DATE( (CAST((FG_End_Date) as date)),'MM/dd/yyyy') AS D1, 
        TO_DATE( (CAST((NDI_End_Date) as date)),'dd/MM/yyyy') AS D2  
    FROM 
        (SELECT * FROM (SELECT slA.identity_id IDA, slA.display_name Display_Name_FG, xmltype(slA.attributes).extract('/Attributes/Map/entry[@key="End_Date"]/@value').getStringVal() AS FG_End_Date 
        FROM spt_link slA where slA.application='6466b2818a2216d2818a24c448f504ef') WHERE IDA='6466b27f8b2b1658818b38d7576078ff' AND FG_End_Date IS NOT NULL AND length(FG_End_Date) > 1),

        (SELECT * FROM (SELECT slB.identity_id IDB, slB.display_name Display_Name_NDI, xmltype(slB.attributes).extract('/Attributes/Map/entry[@key="hreffectiveenddate"]/@value').getStringVal() AS NDI_End_Date  
        FROM spt_link slB where slB.application='6466973f7f3d107f817f3de0d0ba0115') WHERE IDB='6466b27f8b2b1658818b38d7576078ff' AND NDI_End_Date IS NOT NULL AND length(NDI_End_Date) > 1) 
    WHERE IDA=IDB)  
WHERE D1=D2 AND ROWNUM <= 5;

This query is parsing both dates successfully and returning in same format, then the check D1=D2 is passed and below record is returned

Display_Name_FG = 999123123
Display_Name_NDI= Dean, Jimmy Jackson (Jimmy)
IDB	=  6466b27f8b2b1658818b38d7576078ff
FG_End_Date	=  04-11-2026
NDI_End_Date = 04-11-2026

This query is returning one record because in both inner queries IDA=‘6466b27f8b2b1658818b38d7576078ff’ and IDB=‘6466b27f8b2b1658818b38d7576078ff’ is used in Where condition.

Now the problem is if I remove these single identity checks to get all the records using below query:

SELECT * FROM (
    SELECT Display_Name_FG,Display_Name_NDI, IDB, 
        TO_DATE( (CAST((FG_End_Date) as date)),'MM/dd/yyyy') AS D1, 
        TO_DATE( (CAST((NDI_End_Date) as date)),'dd/MM/yyyy') AS D2  
    FROM 
        (SELECT * FROM (SELECT slA.identity_id IDA, slA.display_name Display_Name_FG, xmltype(slA.attributes).extract('/Attributes/Map/entry[@key="End_Date"]/@value').getStringVal() AS FG_End_Date 
        FROM spt_link slA where slA.application='6466b2818a2216d2818a24c448f504ef') WHERE FG_End_Date IS NOT NULL AND length(FG_End_Date) > 1),

        (SELECT * FROM (SELECT slB.identity_id IDB, slB.display_name Display_Name_NDI, xmltype(slB.attributes).extract('/Attributes/Map/entry[@key="hreffectiveenddate"]/@value').getStringVal() AS NDI_End_Date  
        FROM spt_link slB where slB.application='6466973f7f3d107f817f3de0d0ba0115') WHERE NDI_End_Date IS NOT NULL AND length(NDI_End_Date) > 1) 
    WHERE IDA=IDB)  
WHERE D1=D2 AND ROWNUM <= 5;

Then getting below error:

ORA-01843: not a valid month
01843. 00000 - “not a valid month”
*Cause:
*Action:

3

Wondering if these are optional attributes and trying to parse a null value

4

I think there is some data issue in the date value which you are getting from FG_End_Date and NDI_End_Date

5

I looked into more on data… looks to me like Our database is Oracle and default string parsing to date supported is DD-MM-YYYY while our string date is in MM-DD-YYYY format.

Example: Below query is working fine because the string is in DD-MM-YYYY format:
Select TO_DATE( (CAST((‘13-08-2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘13-AUG-2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘13/08/2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘13/AUG/2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;

Below query not working because the string is in MM-DD-YYYY format:
Select TO_DATE( (CAST((‘03-18-2024’) as date)),‘mm/dd/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘03/18/2024’) as date)),‘mm/dd/yyyy’) AS D1 from dual;

Any way I can convert the string ‘03-18-2024’ in to ‘18-03-2024’ then the issue might be resolved.

6

I looked into more on data… looks to me like Our database is Oracle and default string parsing to date supported is DD-MM-YYYY while our string date is in MM-DD-YYYY format.

Example: Below query is working fine because the string is in DD-MM-YYYY format:
Select TO_DATE( (CAST((‘13-08-2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘13-AUG-2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘13/08/2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘13/AUG/2024’) as date)),‘dd/mm/yyyy’) AS D1 from dual;

Below query not working because the string is in MM-DD-YYYY format:
Select TO_DATE( (CAST((‘03-18-2024’) as date)),‘mm/dd/yyyy’) AS D1 from dual;
Select TO_DATE( (CAST((‘03/18/2024’) as date)),‘mm/dd/yyyy’) AS D1 from dual;

Any way I can convert the string ‘03-18-2024’ in to ‘18-03-2024’ then the issue might be resolved.

7

Thank you every one for your valuable replies. I converted both dates in epoch and then was able to compare. Here is the updated query which is working fine for me:

SELECT * FROM (
    SELECT Display_Name_FG,Display_Name_NDI, IDB as Identity_Id, 
        (to_date(FG_End_Date, 'MM/DD/YYYY') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60 AS FG_EPOCH, 
        (to_date(NDI_End_Date, 'DD/MM/YYYY') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60 AS NDI_EPOCH,
        FG_End_Date,NDI_End_Date 
    FROM 
        (SELECT * FROM (SELECT slA.identity_id AS IDA, slA.display_name AS Display_Name_FG, xmltype(slA.attributes).extract('/Attributes/Map/entry[@key="End_Date"]/@value').getStringVal() AS FG_End_Date 
        FROM spt_link slA where slA.application='6466b2818a2216d2818a24c448f504ef') WHERE FG_End_Date IS NOT NULL AND length(FG_End_Date) > 1),

        (SELECT * FROM (SELECT slB.identity_id AS IDB, slB.display_name AS Display_Name_NDI, xmltype(slB.attributes).extract('/Attributes/Map/entry[@key="hreffectiveenddate"]/@value').getStringVal() AS NDI_End_Date  
        FROM spt_link slB where slB.application='6466973f7f3d107f817f3de0d0ba0115') WHERE NDI_End_Date IS NOT NULL AND length(NDI_End_Date) > 1) 
    WHERE IDA=IDB)  
WHERE FG_EPOCH<>NDI_EPOCH AND ROWNUM <= 5;