Issue with Time Periods in Database View

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-11-2023 01:09 AM - edited 10-11-2023 01:09 AM
Good morning,
Does anyone know if you can use syntax such as javascript;gs.beginningOfThisMonth() in the where clause of a database view, and does it work with sys_created_on?
To validate ahead of our use case, we created a simple database view to show incidents related to problems by joining problem to incident (left join), where inc_problem_id = prob_sys_id. This returns some 2500 records, but if we try and narrow it to just incidents created last month (about 200) we either get the same number of records or nothing at all. We have tried:
inc_sys_created_on>'2023-09_01 00:00:00'&&inc_problem_id=pb_sys_id - returns 2500 records
(inc_problem_id=pb_sys_id) && (inc_sys_created_on> javascript;gs.beginningOfThisMonth()) - returns zero
(inc_problem_id=pb_sys_id) &&(inc_sys_created_on>=javascript;gs.beginningOfLastMonth()^inc_sys_created_on<javascript;gs.beginningOfThisMonth()) - returns zero
Not sure if its an issue with the javascript command not being supported in database views or whether we have been looking at this too long now and our logic has gone awol!
Any advice appreciated thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-11-2023 02:30 AM
Hi Cirrus,
Please check the below post and see if it helps.
https://www.servicenow.com/community/developer-forum/database-view-date-parameter/m-p/1417336
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-11-2023 03:06 AM
Could you please try this:
(inc_problem_id=pb_sys_id) && (inc_sys_created_on> This month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth())
Try this and let me know.
Regards,
Shamma