Converting a decimal to a date issue/question

Discussion forum about all things Team Developer 5.x and 6.x
Headcold
United States of America
Posts: 25
Joined: 05 Mar 2018, 16:01
Location: USA

Converting a decimal to a date issue/question

Post by Headcold » 14 May 2020, 15:14

In my table i am pulling two fields that are saved as a decimal but hold a date. So doing any kind of date logic will not work in the query. I want to take both the date fields and if one is greater than 3 days of the other then dont display. I am not sure why a decimal field was used over a date field but I am sure they had there reasons this was created many years ago and has a ton of data stored. So changing the data type in the table is not an option.

So as i have said before i am fairly new to Team Developer. So please excuse my ignorance. I am wondering is there a way to pull the data from the database one row at a time and use an action to compare the two fields and if one date field is greater than 3 days of the other then show and than loop thru and continue to check until done.

Maybe some know of an example out there I could look st that is similar? actually any tips or advice would be great.

Thank you

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 2928
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

Re: converting a decimal to a date issue/question

Post by Dave Rabelink » 15 May 2020, 06:51

You should first identify the kind of decimal date/time which is used.

Probably it is UNIX time.
What that is: https://en.wikipedia.org/wiki/Unix_time

An online conversion tool to convert between UNIX and "readable" date/time
(to have a check for you to see if the dates in your DB are in fact UNIX datetimes)

https://www.unixtimestamp.com/index.php
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

thomas.uttendorfer
Site Admin
Site Admin
Germany
Posts: 198
Joined: 05 Mar 2017, 17:19
Location: Munich Germany

Re: Converting a decimal to a date issue/question

Post by thomas.uttendorfer » 15 May 2020, 07:14

Hi,
I am wondering is there a way to pull the data from the database one row at a time and use an action to compare the two fields
In a table you can use the Message "On SAM_FetchRowDone" which is fired whenever a new row is fetched. Here is the best place to manipulate the currently fetched data.

Regards Thomas
Thomas Uttendorfer
[ frevel & fey ] Software-System GmbH
https://thomasuttendorfer.wordpress.com/

Headcold
United States of America
Posts: 25
Joined: 05 Mar 2018, 16:01
Location: USA

Re: Converting a decimal to a date issue/question

Post by Headcold » 15 May 2020, 09:32

thanks guys

Headcold
United States of America
Posts: 25
Joined: 05 Mar 2018, 16:01
Location: USA

Re: Converting a decimal to a date issue/question

Post by Headcold » 18 May 2020, 16:57

Can you manipulate each row as it is fetched before it is displayed in the table? Say I only want to show the row of data if the some of two fields are equal.Than display the row and move onto the next row and evaluate it.

thomas.uttendorfer
Site Admin
Site Admin
Germany
Posts: 198
Joined: 05 Mar 2017, 17:19
Location: Munich Germany

Re: Converting a decimal to a date issue/question

Post by thomas.uttendorfer » 19 May 2020, 14:00

Hi Headcold,

yes you can do that.

Don't use SalTblPopulate.
Instead load your data in variables.
I recommend creating a functional class
with local variables to hold colum-data and use a dynamic array of those functional classes
to hold all the rows you want.

Fetch the data to the variables of your functional class.

If you'd like to add a row to the table use
nRow = SalTblAddRow(..)
Call SalTblSetContextRow( .., nRow)
then set the data of the columns with the data of a member or the array mentioned above.

Regards Thomas
Thomas Uttendorfer
[ frevel & fey ] Software-System GmbH
https://thomasuttendorfer.wordpress.com/

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 137
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Converting a decimal to a date issue/question

Post by Steve Leighton » 20 May 2020, 23:01

.
Yes Thomas suggests a nice method of fetching / populating a Table Window / Grid, with the ability to easily manipulate the data as it is stored in the class* .

Using arrays of Functional classes is good style - bearing in mind that you are in effect 'caching' the data in memory *.
* See my alternate in-memory cache method suggestion below.

Which is great for lookup data which rarely changes.

However - if the data is very dynamic and changes a lot, and you need up-to-date data in your Table Window / Grid, the array of functional classes ( the 'cache' ) will need refreshing every time you want to populate the Grid again.
This is an extra step in the process - even if the data is updated via the Table Window / Grid --> then your in memory cache needs updating at the same time.

Small point, but extra work that needs mentioning. Been there done that .

p.s. *An alternate to Functional Classes being used as a cache to populate a Table Window ( or any kind of in-memory data storage ), consider using a local in-memory SQLite database as the cache,
which I found to be faster ( and easier to keep the cache up-to-date )
It can mirror your main data-source, and takes minimal foot-print in your run-time.
Populating the Table Window can then be done by using standard SQL to extract a sub-set from SQLite, and manipulate each row as it is extracted and before its added to the TW.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Return to “General Discussion”

Who is online

Users browsing this forum: [Bing], [Ccbot] and 0 guests