Database view date parameter

[rlatorre]

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 · Accepted solution]

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

[Naveen87]

@Manish Vinayak1 ,

When I run with query 

inc_contact_type!="Event" it works fine but when I run with query

inc_contact_type!="Event"&&inc.sys_created_on>= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

it givse me below error.

can you please suggest?

Thank you.

[rlatorre]

Received this error trying to use the date parameter on the metric instance table:

Tried with no error but returned no records:
mi_definition = md_sys_id && mi_sys_created_on >= SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Records are returned without the date parameter.

[Manish Vinayak1]

Hello,

As mentioned in the example above, you don't need the SELECT keyword. Try the following where clause:

mi_definition = md_sys_id && mi_sys_created_on >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

You can increase the interval to 6 months as per your requirements.

Give it a try. Hope this helps!

Cheers,

Manish