Date conversion question/issue

FranklinAlexander — Sun, 12 Dec 2021 10:31:23 GMT

I have a table stored in a SQL database with a date field that is formatted as such: yyyy-mm-dd. Since I cannot query the table directly from the client site, a view table was created and housed in an esri gdb, then published to ArcGIS REST. For some reason (I didn't create the view table), the date field format in the view table is d/m/yyyy. Now I am querying the data using the REST service url from the query widget.js file using queryTask. What is being returned is an integer for the date, which is fine except the integer being return converts to 1939-12-31, when the correct date (in the database) is 1940-01-01.

After playing around with this, I have determined that the javascript date function seems to like the d/m/yyyy format, because that format gets translated correctly, and the yyyy-mm-dd format is one day behind ( I get two different integers). This tells me that when queryTask looks at the view table date field (which has the correctly formatted date), it must actually be seeing the SQL database table field with the format it doesn't like.

My question is how do I solve this? I tried this:

d = new Date(stats2.KillDate);

let yy = d.getFullYear().toString();

let mm = (d.getMonth()+1).toString();

let dd = (d.getDate()+1.toString();
‍‍‍‍‍‍‍

This works ok most of the time, but it's not 100%. In the case of 1940-01-01, I get 1939-12-32

One last bit of info:

1940-01-01 (stored as date field in SQL database) returns the date integer -946771200000 (converts incorrectly)

1/1/1940 (stored as date field in esri gdb table) would return the better date integer -946753200000 (converts correctly)

This can't be the first time this has come up and I am sure there is probable some looong solution (using js switch/case?), but if anyone knows a better way of handling this please advise.

Thanks!!

Re: Date conversion question/issue

FranklinAlexander — Sun, 12 Dec 2021 10:31:26 GMT

Here is the solution I came up with and it seems to be working (at least on all of the results I have checked so far):

let d = new Date(stats2.KillDate);
d.setDate(d.getDate() + 1);
console.log("converted date ", d);
let yy = d.getFullYear().toString();
let mm = (d.getMonth()+1).toString();
if(mm.length === 1) {
    mm = '0' + mm;
}
let dd = d.getDate().toString();
if(dd.length === 1) {
    dd = '0' + dd;
}
let kd = yy + '-' + mm + '-' + dd;‍‍‍‍‍‍‍‍‍‍‍‍‍

I am always open to better solutions

topic Re: Date conversion question/issue in Web AppBuilder Custom Widgets Questions

Date conversion question/issue

Re: Date conversion question/issue