Back to Community Threads
1
Transaction Times are sometimes recorded as 00:00 in the database.
Problem reported by Stan Paulauskas - 4/6/2022 at 5:29 PM
Resolved
I'm using Atrex-19 and pulling the data via an ODBC connection. I'm seeing that the {ord, invoice}.TransTime is sometimes being recorded as 00:00, but mostly null. It seems that the Transaction time checking was added sometime in 2005? I don't see any recent cases where this value is 00:00 or null. 

5 Replies

Reply to Thread
1
Terry Swiers Replied
4/6/2022 at 6:03 PM
Employee Post Marked As Resolution
The TransTime field was added in Atrex 11, so the value of that field for any transaction created before that will be null.  Editing one of the old transactions with a null time with a newer version will set the TransTime field to zero when it's saved as 00:00.
0
Stan Paulauskas Replied
4/6/2022 at 6:07 PM
Got it. is there any way to change that default behavior so that it records a consistent format with the new data?
0
Terry Swiers Replied
4/6/2022 at 7:25 PM
Employee Post
You have 3 options:

1. update the retrieval query to access the transtime field separately using  Coalesce(Invoice.TransTime, Time '00:00') to convert null values to a time value.

2. update the table and set all null transtime to   TIME '00:00'

3. update the table and set all of those with a time value of 00:00 to null 
0
Stan Paulauskas Replied
4/7/2022 at 5:38 AM
Number 3 won't help if somebody edits that invoice again, right? I'll take a look at replacing the values in the query. Thanks!
0
Stan Paulauskas Replied
4/7/2022 at 6:13 AM
Came up with the following select statement to resolve the issue:
SELECT CASE TransTime WHEN TIME '00:00' THEN NULL ELSE TransTime END TransTime FROM invoice;
 It isn't pretty, and gets obnoxiously long since I have to pull all of the columns for the invoice, ord, etc. tables. But it resolves the issue of inconsistent data handling.
Back to Community Threads

Reply to Thread