Hi,

I'm using GridView to get data from SQL database. It works great, except I found out today that the datetime that it is reading shows correctly when viewing. But when you use that information in any calculation, anything past noon will round it up to next day.

Example:

Datetime of "2018-01-29 12:20 PM", if I use a calculated field and put this data in there, it will show "2018-01-30 00:00 AM"

For datetime of "2018-01-29 11:59 AM", if I put this in calculated field, it will show as "2018-01-29 00:00 AM"

Is there a way around for it not to round up?

Marlon

I found a work around.

You'll need two windows grids, one for reading and viewing the datetime, the other will getlinkvalue the datetime. This way, it does not round up and from there I can use it in another calculated field without rounding up.

Anyway, can you folks do a patch or make it a part of improvement on future release if you have not done so on GridView 9.3.

Marlon

Glad you came up with a work around, in order to better define the issue please post :

1) Date of ADS32.DLL in the Softrak\System folder

2) Specific "Type" of SQL date column from below
[DateTime1 - smalldatetime]
[DateTime2- time(7)]
[DateTime3- date]
[DateTime4- datetime]
[DateTime5- datetime2]
[Datetime6- datetimeoffset]


3) Format used in the Gridview column definition

Another work around on the SQL side would be to insert a column that converted the datetime column to and INTEGER and use that in Gridview

This is the first report of this behavior and was NOT addressed in GV 9.3A

Hi Marlon,

I'm glad you found a workaround.

I think that SQL stores the time as a fractional portion of the number. Try taking the INT() of the date and see if that results in a different value.

Michael M,

1. Ads32.dll - 08/04/2017
2. The SQL Column data type is "datetime"
3. Format used in GridView
a. MM/DD/YYY
b. MM/DD/YY HH:MM

Thanks for the suggestion, but the SQL database is from another 3rd party application. So, no can't change.

Marlon

Hello Andrew,

Yes, you're right. I tried the Int() function, but the result is the same.

As soon as you put the datetime column in a calculated field without any function, it rounds up to a whole number.

Marlon

Thanks for the info: we will check this out for a future release.

I created a SQL Table with datetime4 as datetime with 2 records:

2018-01-02 11:59:00.000
2018-01-02 12:59:00.000

Using Gridview 9.3A and a DSN pointing to the SQL database I get:

02.01.2018
02.01.2018

in a calculated column I am able to duplicate your issue

02.01.2018
03.01.2018 <== error

Something happening in the formula engine. Will let you know when a "fix" is available.

Hi Marlon,
The issue of Datetime being rounded up when used in a calculation has been resolved in Gridview 9.3B (2018-11-07)