How to query Date/Time field?

[hyperjam]

Hello, I'm trying to check whether there already exists a record with certain date, but any query which i try in background scripts gives "no", even if the record exists.

Can you please point to the error i'm doing in the query?

var gdt = new GlideDateTime();  

gdt.setNumericValue(sec);

var checkDate = gdt.getValue();

var gr = new GlideRecord('u_sps_availability');

gr.addQuery('u_date',checkDate);

gr.query();

if(gr.next()){

  gs.print("yes");

        }

else { gs.print("no"); }   // the results of the query, even if the date exists

[Gurpreet07 · Accepted solution]

Hi Anna,

It seems like you have declared a variable sec and your are setting the same in gdt. The issue will be the time component of the date. Please use getDate method to get date alone and compare using startswith.

var gdt = new GlideDateTime();

gdt.setNumericValue(sec);

var checkDate = gdt.getDate();           //getting only date component

var gr = new GlideRecord('u_sps_availability');

gr.addQuery('u_date','STARTSWITH',checkDate);             //Compare using startswith

gr.query();

if(gr.next()){

  gs.print("yes");

        }

[hyperjam]

Hello,

With getDate() query finally give normal result. thank you!

But maybe you know, what to do with time?cause i would like the date/time to match exactly?

[Gurpreet07]

Hi Anna,

Date/time is something to be compared over a range of time. Time changes repeatedly and thus exact comparison is hard to achieve. Generally we do check weather a date falls between two other dates... In your case you are having only two date fields and thus either you could compare only date component or you could check if the 2nd date is close enough to first date... Following code is creating a range of date ..   checkDateStart (60000 milliseconds before actual date),     checkDateEnd(60000 milliseconds after actual date).       You can reduce the duration for more accurate results but further decrease may lead to incorrect comparison as well.

var duration = 60000 ;       //   milliseconds.

var gdt = new GlideDateTime();

gdt.setNumericValue(sec);

gdt.subtract(duration) ;          

var checkDateStart = gdt.getValue();           //1 minutes before actual date

var gdt2 = new GlideDateTime();

gdt2.setNumericValue(sec);

gdt2.add(duration) ;  

var checkDateEnd = gdt2.getValue();           //1 minutes after actual date

var gr = new GlideRecord('u_sps_availability');

gr.addQuery('u_date','>',checkDateStart);             //Compare using startswith

gr.addQuery('u_date','<',checkDateEnd);             //Compare using startswith

gr.query();

if(gr.next()){

  gs.print("yes");

        }

There is another workaround for such comparisons and that is .. truncate first date for seconds component and new date string is having only YYYY:DD:MM HH:MM . Now you can should be able to use startswith operator to compare values.