Expand my Community achievements bar.

We are excited to introduce our latest innovation to enhance the Adobe Campaign user experience — the Adobe Campaign v8 Web User Interface!
SOLVED

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

Avatar

Level 2

 

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

 

ManjulaPongiappan_0-1702472696819.png

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!!.

 

1 Accepted Solution

Avatar

Correct answer by
Level 2

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. 

View solution in original post

7 Replies

Avatar

Community Advisor

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

Avatar

Community Advisor

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

Avatar

Level 2

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.

 

 

ManjulaPongiappan_0-1702580324267.png

 

Could you please help me to find the issue.

 

Thanks in advance !!.

 

Avatar

Level 6

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

Avatar

Administrator

Hi @ManjulaPongiappan,

Was the given solution helpful to resolve your query or do you still need more help here? Do let us know.

Thanks!



Sukrity Wadhwa

Avatar

Correct answer by
Level 2

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.