How to Set Relative Date Condition in Where Clause of Database Views?

[Nia McCash]

I have tried: j_sys_created_on > (DATE_ADD(NOW(),INTERVAL -7 DAY))

But I keep getting the error:

Where clause in view u_journal_test has an invalid field or a field that is not visible (DATE_ADD)

Is there a way to set a relative date condition in the where clause of a database views table?

[Nia McCash]

So I'm providing a work around for my own question here. Feel free to comment on the pros and cons of this workaround...

I have created a Scheduled Job to update the where clause in my Database Views table definition on a daily basis.   The script is something like this:

var dbViewTable = new GlideRecord('sys_db_view_table');
if (dbViewTable.get('[the sys_id]')) {

   var relativeDate = new GlideDateTime();
   relativeDate.addDaysLocalTime(-7);

   var whereClause = "j_sys_created_on > " + relativeDate.getDisplayValueInternal();
   dbViewTable.setValue('where_clause', whereClause);
   dbViewTable.update();
}

[JPlace]

Hi Nia,

You actually helped me with this. I can confirm your condition worked for me from requested item table( Shown Below). Hope this helps and thank you sir!

Best Regards,

-John

[JPlace]

Sorry, it is late. Believe Nia is girls name 🙂

[Kathrina Yvonne]

Hi! I saw that you're working on a requested item table. May I ask if it's possible to show in a report consisting of the Requested Item table (sys_req_item) and the History table (sys_history_line) using the Database View?  I can't seem to figure out what to put in their Where Clause. I cant figure out how to connect the two tables. Is it possible that I need another table? (just like the incident and metric instance, wherein the other table is metric definition)

Also, what do I put in a where clause if I want the table to return only the records for last month?