Just a little hint if you deal with SQL databases that have datetime data types (like Oracle).
If you import these into the dictionary, Clarion will add them as the clunky:
dt string(8)
dt_group, over(dt)
dt_date date
dt_time time
which is good and works in all situations. But if you know that your data will never have a time component, it is possible to declare the field in your Clarion dictionary, just a straight dt date
The only real downside of this is that if you do somehow ever get a time value in the datetime field, you will get a ārecord changed by another workstationā error if you try to update the row. A real advantage is that Clarion will be much better about using that plain date field for filtering, ordering, etc.
And thats the catch, the data in these dbs have a date time, so technically they have a date time.
Perhaps if it was worded, āif your program never needs to work with date time thenā there would be justification in using your suggestion, but simply not displaying the time element would suffice.
I think my āif you know that your data will never have a time componentā is stronger than your
āif your program never needs to work with date timeā. Yours is" my program will never write, and will never want to read a time value", mine is āno one, not my program nor anyone else, will ever put a time value inā.
In Oracle you donāt have a choice: if you want a date you get a time too. But if you donāt care about the time then in addition to not displaying it in your program, you donāt need it in your dictionary, either. So long asā¦Thatās all Iām saying.
Found this by accident when I changed a file definition from SQLite to Oracle. The SQLite date field (which does actually accept a time component) was translated as a date column in Clarion. Everything looked good, but I ran into problems when someone tried to change a record, cos the table had been populated from another Oracle table and there was a time component. And Nov 1, 2024 is not the same as Nov 1, 2024 10:34 am, so the update failed.
It is nice to have the choice, but if you do actually care about the time, datetimes are the way to go. You can just subtract one from the other to get the interval between them: no fooling around multiplying the date different by 24 hours and adding it to the time difference.
Curious how your Oracle date experience compares to PG? I use both timestamp fields and date fields in PG, but canāt remember ever using a date filter on a timestamp field.
And thats probably because of how its written. Ive only looked briefly at oracle once, and deutche bank use it according to someone I used to know.
Got no interest in it, but I can see how its good for bank statements and other transactions, despite banks taking standing orders and direct debits out first before paying money into an account. For banks, its a legal form of arbitrage. If banks had to work on first come first serve, then would you see more bank runs? Probably.