Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread Date conversion problem
Thu, Aug 11 2016 7:40 AMPermanent Link

Bill

Hi all,
my web application has an Asp.net server backend.
Server produces json answer and read requests serializing and deserializing with famous free Json.Net library.
My problems arise with dates and datetime data.
Serving MS dates produces wrong values (1/1/1970 and various) and millisecond conversions (from Json.net library) in json produce an error in javascript during dataset loading.
Can someone point me into the right direction for right data exchange?
I read the guide but I didn't come out...
There is some experiences with Visual Studio 2010 and newer as a backend?
Thanks in advance.
Thu, Aug 11 2016 8:29 AMPermanent Link

Matthew Jones

Bill wrote:

> my web application has an Asp.net server backend.
> Server produces json answer and read requests serializing and
> deserializing with famous free Json.Net library.  My problems arise
> with dates and datetime data.  Serving MS dates produces wrong values
> (1/1/1970 and various) and millisecond conversions (from Json.net
> library) in json produce an error in javascript during dataset
> loading.  Can someone point me into the right direction for right
> data exchange?  I read the guide but I didn't come out...  There is
> some experiences with Visual Studio 2010 and newer as a backend?

I'm using the WebAPI and JSON.Net, but have not had any problems. What
are you actually sending over the wire? (Chrome F12 should show you the
content.) My code is, it appears, doing all automatic conversion, but I
think I saw there was an option for translating dates, but that might
be in EWB. Indeed, have a look at TDateTimeFormat = (dtfRaw,dtfISO8601)
and I think I always specify the latter in the TReader constructor.

--

Matthew Jones
Thu, Aug 11 2016 9:39 AMPermanent Link

Walter Matte

Tactical Business Corporation


Date and Time fields are milliseconds from 1/1/1970 (unix like) as noted from EWB Manual.

Date and Time are localize (option of dataset) - time zone.

See this thread

http://www.elevatesoft.com/forums?action=view&category=ewb&id=ewb_general&page=1&msg=10300#10300

Walter
Thu, Aug 11 2016 10:27 AMPermanent Link

Bill

Thanks for quick answering.
Let me better explain what I'm doing.
1) I produced a design time dataset connecting to my SqlServer Db (in the preview data tabsheet I see correctly all the datetime columns);
2) I created a form where I put dataset and a grid component connected to the dataset;
3) in my visual studio I produce a special webform that produce (starting from a parametered LINQ query, a dataset) a json dataset; briefly I can do:

3.1) string json = "{ \"rows\":" + JsonConvert.SerializeObject(myLinqQuery, new JsonSerializerSettings { DateFormatHandling = DateFormatHandling.IsoDateFormat}) + "}";

3.2) string json = "{ \"rows\":" + JsonConvert.SerializeObject(myLinqQuery, new JsonSerializerSettings { DateFormatHandling = DateFormatHandling.MicrosoftDateFormat}) + "}";

4) In EWB, I set Database.BaseUrl to properly connect to that page, set dataset parameters and call Database.LoadRows(myDataset).
If I use the 3.2 solution, my JS Client doesn't trow exception but show all "1/1/1970 0:00AM" dates
If I use the 3.1 solution (as I did reading post suggested by Walter) asp.net produce DATEField":"\/Date(1309557600000+0200)\/" and my jsclient trow an exception in converting (dataset.doLoadError).

Matthew, where can I set TDateTimeFormat using a Dataset to set the right behavior? Is an application setting?
????.DateTimeFormat := dtfISO8601;

Many thanks again to all.
Bill
Thu, Aug 11 2016 11:04 AMPermanent Link

Matthew Jones

Bill wrote:

> 3.1) string json = "{ \"rows\":" +
> JsonConvert.SerializeObject(myLinqQuery, new JsonSerializerSettings {
> DateFormatHandling = DateFormatHandling.IsoDateFormat}) + "}";
>
> If I use the 3.1 solution (as I did
> reading post suggested by Walter) asp.net produce
> DATEField":"\/Date(1309557600000+0200)\/" and my jsclient trow an
> exception in converting (dataset.doLoadError).

Interesting. I just added this to an object:
       public DateTime JSONTestTime { get; set; } = DateTime.Now;
And serialised it with:
var document = JsonConvert.SerializeObject(userItem, new
JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore
});
And it outputs:

"JSONTestTime":"2016-08-11T15:59:31.4737647+01:00"

Which is a standard ISO type. I just tried with the DateFormatHandling
= DateFormatHandling.IsoDateFormat added, and I get the same format.

That's what you should be getting. I'd perhaps look at the LINQ to see
if that is doing something interesting with auto-converting the
datetime type.

--

Matthew Jones
Thu, Aug 11 2016 12:28 PMPermanent Link

Walter Matte

Tactical Business Corporation


Example json I create - this is what Database.LoadRows(TDataset,false) expects.    

RateDate is a Date field.

{ "rows": [
{"URid": 4,
"UserListId": 128,
"RateDate": 1378080000000,
"RateType": "Hourly",
"Rate": 50,
"CurrencyUnit": "CAD"}
] }


Walter
Fri, Aug 12 2016 2:58 AMPermanent Link

Bill

Ok, thanks.
It's beginning an august nightmare... but I hope i missed something...

In the server side JSON.NET has many formatters and I found a JavaScriptDateTimeConverter that produces
... "LogDate":new Date(1234656000000) ...
in the javascriptclient (produced by EWB) dataset expect a string done as:
.... "LogDate": 1378080000000 ....
Obviously, i would like to write minimum code. So the question is:

is there an easy way to use a different coding (i.e. ISO) to EWB Database.LoadRows method?
I think that Database uses TReader to get data, probably.

And, but it's off-topic here, is there an easy way to specify JSON.net a formatting way to produce right data for the client?

Thanks again.

Sorry for the newbie question but it could be useful to other too.
Fri, Aug 12 2016 4:28 AMPermanent Link

Matthew Jones

Bill wrote:

> is there an easy way to use a different coding (i.e. ISO) to EWB
> Database.LoadRows method?  I think that Database uses TReader to get
> data, probably.

I'd certainly focus on that - ISO date format is standard, so getting
EWB to accept that, even if it accepts its own too, would be the way
forward. And that should actually be simple - as you have the full
source to the library, you just need to find the appropriate code, copy
the file to a place your project sees it first (the settings allow
this), and then you can edit to improve it. My suggestion would be to
look for the T in the string, and if present, treat as ISO, otherwise
assume EWB form. This keeps your API standard.

Tim is also open to suggested improvements, and has taken my code as
proof of concept in the past. (One he has determined doesn't fit
general use, so I apply that each time, and it is falling out of need
here too.)

--

Matthew Jones
Sat, Aug 13 2016 2:27 AMPermanent Link

Bill

Just a work in progress to verify what could be done and where...
It seems ISO date coding is better for human readability.
So: changed a copy of WebData.wbs

procedure TDataRow.Parse;
[....]
              else if (TempColumn.DataType=dtDate) or (TempColumn.DataType=dtTime) or
                      (TempColumn.DataType=dtDateTime) then
                   begin
Line 907        // - Values[TempColumn.Index].AsInteger := StrToInt(TempColumnValue);
                     Values[TempColumn.Index].AsDateTime := ISOStrToDateTime(TempColumnValue);
[...]

Next step could be inherit classes and extend Dataset component...
Sat, Aug 13 2016 4:58 PMPermanent Link

erickengelke

Avatar

> Just a work in progress to verify what could be done and where...
...
>Next step could be inherit classes and extend Dataset component...

My next book release shows how to change it through inheritance.  That won't be available
until...probably a few weeks.

Erick
Image