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

[ramireddy]

Hi,

If it's datetime column, "Time" also needs to exactly match, so if you are performing search with date alone you need to search like ">= Date and < Date +1", which will give that day records. Check with below syntax(Untested one).

var gdt = new GlideDateTime();

gdt.setNumericValue(sec);

var checkDate = gdt.getValue();

var gdt1 = new GlideDateTime();

gdt1.setNumericValue(sec + 86400); // adding 86,400 seconds = 1 days

var checkToDate = gdt1.getValue();

var gr = new GlideRecord('u_sps_availability');

gr.addQuery('u_date','>=',checkDate);

gr.addQuery('u_date','<',checkToDate);

gr.query();

if(gr.next()){

  gs.print("yes");

        }

[Midhun1]

Hi Anna,

Just pass the value in sec:

var gdt = new GlideDateTime();

gdt.setNumericValue(1314777600000);

gdt.getValue();

gs.print("date"+gdt.getValue());

[Rajesh T]

This line is wrong. gdt.setNumericValue(sec);

Please pass the value in milliseconds.

Example :

var gdt = new GlideDateTime();
gdt.setNumericValue(1314777600000);
gs.print(gdt.getValue());

Output:

*** Script: 2011-08-31 08:00:00

[Asis Bagga]

Yes the value needs to be in milliseconds. For me seconds did not worked.