How to compare date values (which is in different formats) in QueryDef in JS activity in ACC | Community
Skip to main content
M
ManjulaPongiappan
Level 2
December 13, 2023
Solved

How to compare date values (which is in different formats) in QueryDef in JS activity in ACC

  • December 13, 2023
  • 4 replies
  • 2320 views

 

Hi,
When I try to compare the date values in QueryDef in Java script activity (in Adobe Campaign Classic), Its throwing an error. Because the date format are not matching.

Date1 format: Thu Dec 07 2023 18:45:26 GMT+0530 (IST)
Date2 format: 2023-12-11 07:01:34.493Z

 

Now I need to convert the date1's format to compare it with date2 field value. Is anyone please help me with the code(syntax) to convert the date(in this format: 2023-12-11 07:01:34.493Z). 

2. Will the above date comparison logic work? If iNO, could you please help me with the correct syntax.

 

Thanks in advance!!.

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by ManjulaPongiappan

Thank you for the reply!!

 

Now I have changed the approach(added query activity before JS to add the filter condition there itself). And it's working as expected. 

4 replies

Manoj_Kumar
Community Advisor
Manoj_KumarCommunity Advisor
Community Advisor
December 13, 2023

Hello @manjulapongiappan 

 

You can convert the timezone by using an external library like moment js

Moment.js | Home (momentjs.com)

Manoj     Find me on LinkedIn
    Marcel_Szimonisz
    Community Advisor
    Marcel_SzimoniszCommunity Advisor
    Community Advisor
    December 14, 2023

    Hello @manjulapongiappan,

    is this query def in javascript then you can use formatDate function to simple YYYY-MM-DD H:M:S

     

    <where> <condition expr={"@created > " + formatDate(date,"#YYYY:MM:SS %02H:%02N:%02S#")}
    • notice the brackets {} if you do not use them in xml the variable will not be recognized

    • also have added # # to wrap date (might not be needed)

    The best way how to make it work is to create query manually in query builder then check the syntax in XML source data of the workflow.

    Also take a look at various queryDef options you can use to query data.

     

    Marcel Szimonisz

      M
      ManjulaPongiappanAuthor
      Level 2
      December 14, 2023

      Hi,

       

      Thank you for your quick response!!.

       

      I have formatted the date and it is working as expected. And I have added the below where clause in the query def. The code is running without any error.

       

      But its not filtering/applying the criteria. I am getting all the records in the output.

      i.e if the created date is less than the lastRunDate,still the records are fetched.

       

       

       

      Could you please help me to find the issue.

       

      Thanks in advance !!.

       

      isahore
      Community Advisor
      isahoreCommunity Advisor
      Community Advisor
      January 4, 2024

      Hi @manjulapongiappan,

       

      You should convert both the date fields for comparison to datetime format, and for that you can utilize the OOTB formatDateTime function without specifying any specific date format as that can be erroneous because of the timezone offset between two dates and needs additional logic. Example below:

      <condition expr={"ToDateTime([scheduling/@contactDate]) > #" + NL.XTK.formatDateTime(vars.execStartTime) + "#"} />

       

      Regards,

      Ishan

        M
        ManjulaPongiappanAuthorAccepted solution
        Level 2
        January 31, 2024

        Thank you for the reply!!

         

        Now I have changed the approach(added query activity before JS to add the filter condition there itself). And it's working as expected. 

        Sukrity_Wadhwa
        Community Manager
        Sukrity_WadhwaCommunity Manager
        Community Manager
        January 31, 2024

        Thanks for sharing that @manjulapongiappan!

        Sukrity Wadhwa
        Terms & ConditionsAccessibility statement

        Loading footer...