SQL Time Displayed as Jibberish Letters Instead of Numbers

[ghendrix]

I have a ListGrid that is displaying an MSSQL Time Column as jibberish letters.
The SQL Column is defined as [dtCourt_Time] [time](7) NULL.
The data in this column is '08:30:00.0000000'.
I am calling a vb.net page that is running a MSSQL Sproc to my datasource:

GetCtDateList(parms).Tables(0).WriteXml(Response.OutputStream)

In my isc.DataSource, here is the column along with the column type defined; the errant 'dtCourt_Time' column is bolded and underlined below.:

var dataCourtDates = isc.DataSource.create({
dataURL: 'DataScripts/GetCtDateList.aspx',
recordXPath: "/NewDataSet/DATA",
autoFetchData: false,
autoDeriveSchema: true,
fields: [
{ name: "iBondCourt_ID", hidden: true },
{ name: "sCourt_Name", title: "Court", type: "text" },
{ name: "dtCourt_Date", title: "CourtDate", type: "date" },
{ name: "dtCourt_Time", title: "CourtTime", type: "time" },
{ name: "sCourt_Room", title: "CourtRoom", type: "text" },
]
});

Here is the ListGrid:

isc.ListGrid.create({
ID: "GridCourtDates",
height: "100%",
width: "100%",
dataSource: "dataCourtDates",
autoFetchData: false,

});

For some reason, the ListGrid is rendering the dtCourt_Time column as "PT8H30M" instead of some form of the actual data: '08:30:00.0000000'.
What could I be doing wrong that is making this column display jibberish letters instead of the actual time?

[Isomorphic]

Unfortunately, you've provided all the details but the salient one: what's in the XML data being returned from your dataURL? I think you'll find that it's "PT8H30M", and there's your problem :)

If you are unable to adjust the format of the XML being returned from your server, then there are two different APIs where you can transform the data on the client-side, either per-field or for the whole response:

https://smartclient.com/smartclient-....getFieldValue
https://smartclient.com/smartclient-...nsformResponse

Bear in mind that for saving data (if that applies here), you'll likely have to transform again - this would be DataSource.transformRequest.

[ghendrix]

Thank you for your reply. You're right, I cannot seem to intercept the XML being returned. Can you please expand on your answer a bit more? I'm unclear from the links you provided on how to turn "PT8H30M" into a valid time? Thank you in advance for your help and consideration!

[Isomorphic]

If you're looking for starter code, this is a good one to ask ChatGPT. For example, you can ask like this:

I'm using Isomorphic SmartClient, and I have a DataSource for parsing XML responses from my server, which looks like this:
--
var dataCourtDates = isc.DataSource.create({
dataURL: 'DataScripts/GetCtDateList.aspx',
recordXPath: "/NewDataSet/DATA",
autoFetchData: false,
autoDeriveSchema: true,
fields: [
{ name: "iBondCourt_ID", hidden: true },
{ name: "sCourt_Name", title: "Court", type: "text" },
{ name: "dtCourt_Date", title: "CourtDate", type: "date" },
{ name: "dtCourt_Time", title: "CourtTime", type: "time" },
{ name: "sCourt_Room", title: "CourtRoom", type: "text" },
]
});
--
The problem is that the values for the field of type "time" are coming back from the server like "PT8H30M". Can you show an implementation of DataSourceField.getFieldValue(), added to my DataSource, to do the conversion to the expected time value?

Here is a shared dialog with ChatGPT showing how it responds to this:

https://chatgpt.com/share/005657ee-2...2-698869f9d02e

[Isomorphic]

Just a further note here: the data format being returned by your server is an ISO 8601 duration format, however, it doesn't look like you're trying to store a duration, but rather a time of day.

From a quick look, this is a common hack in aspx, where there is no "time of day" type - only a full DateTime, or a TimeSpan.

If you have the ability to remove the hack, the best approach would probably be a single DateTime field, replacing your CourtDate and CourtTime fields.

[ghendrix]

Wow, thanks so much, that worked well! Looks like I need to learn ChatGPT right away before it takes over my job! :)

[Isomorphic]

We've added support to our time fields for ISO 8601 duration format (e.g. "PT8H30M") even though it’s a bit of a hack for aspx, etc. to use a duration value this way, because it seems to be a common hack.

The fix will be present in nightly builds of SmartClient 13.x dated 2024-08-22 and beyond.