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

[ManjulaPongiappan]

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

[_Manoj_Kumar_]

Hello @ManjulaPongiappan 

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

Moment.js | Home (momentjs.com)

[Marcel_Szimonisz]

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

[ManjulaPongiappan]

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]

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

[Sukrity_Wadhwa]

Hi @ManjulaPongiappan,

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

Thanks!

[ManjulaPongiappan · Accepted solution]

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]

Thanks for sharing that @ManjulaPongiappan!