Database view date parameter

rlatorre · ‎08-26-2019

U'm having trouble adding a date parameter in the where clause.

Created in last 6 months

Can someone provide me with an example?

Manish Vinayak1 · ‎08-26-2019

Hello,

Following is the where clause to show the incidents created in last 6 months when used in a Database View record:

inc.sys_created_on>= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)

inc is the variable prefix for incident table.

Did some trial and error to find out ServiceNow utilizing the MariaDB syntax for those conditions. Here's how you can get last month's date in MariaDB:

SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

Give it a try, and let me know how it goes.

Hope this helps!

Cheers,

Manish

View solution in original post

manoj_s · ‎07-30-2020

Did you find a solution for this?

I cannot use the reports to filter the date as left join will result in incorrect data.

rlatorre · ‎08-28-2019

I should have mentioned that I had tried that and I get this error:

Error Message

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

Manish Vinayak1 · ‎08-28-2019

Oh strange! I tried the same and it worked for me. Here's the screenshot of what I put in my condition:

And here's the resulting database view:

But I wonder if it has to do something with the version. I had tried that in Madrid version, I see that you are on London.

The only change I see is that I used dot walk-ins in my queries.

Ashu Gunjal · ‎02-20-2022

It is not working for me . I have tried same solution

manoj_s · ‎08-03-2020

Where you able to find a solution for adding a date parameter in Database view ?