Query Start Date >= This Year and End Date <= Next Year

[F_bio Gon_alves]

Hi SNC,

I am struggling with a query on resource_plan table.

I have a custom Service Portal widget to which I added this line on the HTML in order to exhibit a label with the number of records using a given filter:

<span class="label label-as-badge label-danger">{{c.data.resourcePlanCount}}</span>

In my Server Script I have the following code:

var resourcePlans = new GlideRecord("resource_plan");

resourcePlans.addQuery("state", "11");

resourcePlans.addQuery("start_date", ">=", "01-01-2017");

resourcePlans.addQuery("end_date", "<=", "01-01-2018");

resourcePlans.addQuery("portfolio", "524ceafddb4b7e0087f371910f9619bd");

resourcePlans.query();

data.resourcePlanCount = resourcePlans.getRowCount();

And here is my problem: the first ("state") and last ("portfolio") queries execute without any problem, but I simply cannot manage to execute the queries to the "start_date" and "end_date" fields.
I know this is not the best way to perform it (using hardcoded dates), but even using this approach I cannot make it work. Am I using the wrong syntax ">=" and "<="?

Thanks in advance,

Fábio Gonçalves

[sergiu_panaite · Accepted solution]

You sure the date format is correct?

So, out of box we have this property that sets the date format:

glide.sys.date_format

value: yyyy-MM-dd

So in my case when I add a record in resource plan start/end look like this:

[F_bio Gon_alves]

Hi Sergiu Panaite,

Thank you so much for your quick reply.
It was precisely that! For some reason (I believe it is related to the new Time Zone we set on our instance), the date fields now appear in the format "MM-dd-yyyy". Even when I add a record plan or some other type of records, the date field appear with this format.

However, that same property you pointed has a value of "yyyy-MM-dd" and for that reason, when I tried to filter using a format like "MM-dd-yyyy" it simply won't work!

Do you have any idea on what might be causing this change on data fields format from the supposed "yyyy-MM-dd" to "MM-dd-yyyy"?

Best regards,

Fábio Gonçalves

[sergiu_panaite]

As a user you can change your date format and this would not have any impact on the global setting:

http://wiki.servicenow.com/index.php?title=Using_Date_and_Time_Fields#User_Preferences

Have you changed yours?

Does your server scripts runs as your user or no user? No user means global, which will take the global setting for the date.

[F_bio Gon_alves]

Hi Sergiu Panaite,

You are right again! 😉

I don't know how, but in fact my data format preferences were changed from "yyyy-MM-dd" to the current "MM-dd-yyyy".

I replace the original "yyyy-MM-dd" format.

Any chance you could see my next two questions on the reply below?

Thank you.

Best regards,

Fábio Gonçalves