4.0.4 - Select Query with a Date comparison Where clause

Hi Meryem,

I am able to get the values by passing formatted date to the select query. Can you try as suggested below?

var dateVar =new Date();
var formattedDate=rbf_formatDate(dateVar , "yyyy-MM-dd HH:mm:ss");
rbf_selectQuery("SELECT name FROM DateTest_1 where myDateTime <='"+formattedDate+"'", 10, function callback(arr){
console.log(arr);
});

Regards,
Shiva

rbf_selectQuery("SELECT id, name, R115577424, days_after, R115577446, activity_priority#code, move_weekend#code, send_email_automated#code, include_signature#code, description, R121904036, R134815371, Attachment_Name, email_template_id FROM activity_template WHERE R115581422="+planId+" ORDER BY days_after ASC", 200, setGridValues);   this query work. Can you try adding + on both side and double quote. Something like rbf_selectQuery('select id, field1, field2 from objectA where myDateTime <=  "+ dateVar.getTime() +"

, 10, function callback(values){

- See more at: community.progress.com/.../24984

Hi Shiva,

Thanks for the reply. it is working.

Now I am  trying to  convert the 'dateVar' to UTC date, before formatting it.

Basically I am doing this select Query on an external table, that has a date column stored in UTC format, and it will always be the case. Whereas the Date that will come form a datePikcer form the page will have the user local time (or whatever the user is setting in the preferences). In my case it is BST Time, but can be IST or US TIME).

So I need to convert the Date I get from the datepicker : 'dateVar', to UTC, then format it and do the query.

What is the best way to do this please?

Thank you

Meryem

Hi Meryem,

In that case,please use the following code:

var givenDate=new Date();
var utcDate = givenDate.toUTCString();

//utcDate is a string and this needs to be converted to Date object since rbf_formatDate accepts only Date object.

//If you want to compare date along with the exact time you can use rbf_formatDate(d,"yyyy-MM-dd HH:mm:ss")
var dateVar=rbf_formatDate(new Date(utcDate), "yyyy-MM-dd");
rbf_selectQuery("SELECT name FROM DateTest_1 where myDateTime <='"+dateVar+"'", 10, function callback(arr){
console.log(arr);
});

Regards,

Shiva

Hello Shiva,

Yes I tried this code :

$(document).ready(function(){

 // date is in the current RB user time zone, BST in my case
  var myDate =   $("#myDateTimePicker").data("kendoDateTimePicker").value();
  console.log('Dater from datePicker is : ' +  myDate);

  // This is returning a string of the UTC date, so in my case it is the RB date - 1 hour (BST = UTC +1)
  var utcDate = myDate.toUTCString();
  console.log('Date converted to UTC is : ' + utcDate);
  
 // This formatting is returning the initial date => looks like we are loosing the UTC conversion when we do new Date(utcDate) 
 var formattedDate= rbf_formatDate(new Date(utcDate) , "yyyy-MM-dd HH:mm:ss"); // this the Date format in the DB.
  console.log('UTC Date formatted before select query is : ' +  formattedDate);
    
   rbf_selectQuery("select id_ from pufReport WHERE Report_Date <= '" + formattedDate + "'  ORDER by Report_Date desc"
                    , 1, function callback(values){     
   }); 
});

</script>

But the behavior is not correct. Console prints of the above code are :

Selected date is : Thu May 19 2016 12:00:00 GMT+0100 (GMT Daylight Time)
Date from datePicker is : Thu May 19 2016 12:00:00 GMT+0100 (GMT Daylight Time)
Date converted to UTC is : Thu, 19 May 2016 11:00:00 GMT
UTC Date formatted before select query is : 2016-05-19 12:00:00

So at the end, we are querying for the string '2016-05-19 12:00:00', in the DB., whereas we should be querying for '2016-05-19 11:00:00'  because the DB dates are in UTC.

I think whenever we do new Date() , it just considers the page time zone, is that correct?

Any suggestions to get the correct UTC date string ? 

Thank you

Meryem

Right, on form submit, DateTime field date is processed as per User's configured timezone (Rollbase User object property) and not the browser timezone.

In this scenario, you need to first determine User configured timezone. For this, you can read user object token for Timezone i.e.{!#CURR_USER.timeZone} . This will return a String literal.

Unfortunately, JavaScript support for timezones is not optimal. You may need to parse this string literal to identify the offset that is GMT + 05:30 (in case user configured timezone is IST). Once you have this offset, you need to make the required correction in DateTime field input value to get to UTC value.

Hope this helps.

Hi Siraj,

Well I managed to do this differently :

I am building the String we are using in the select Query manually, by getting the UTC year, month, day, hour, minute and second, from the User date.

Here is the code :

function buildingUTCString(date){

 var year = date.getFullYear();

 var month = date.getUTCMonth() + 1;

 var day = date.getUTCDate();

 var hour = date.getUTCHours();

 var minute = date.getUTCMinutes();

 var second = date.getUTCSeconds();

 if(month < 10){ month = '0'+ month; }  

 if(day < 10){ day = '0' + day; }  

 if(hour < 10){ hour = '0' + hour; }

 if(minute <10){ minute = '0' + minute; }

 if(second < 10){ second = '0' + second; }

  var dateString = year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + second;

  return dateString;

}

It is easier to do this than get the offset and calculate the date again...

One thing, is this format "yyyy-MM-dd HH:mm:ss",  any likely to change ? Or are the date fields always going to be stored in this specific format in the DB?

Thank you

Meryem