AR_InvoiceHeaderHistory Time Stamp

SOLVED

What are the differences between 'Time Created' and 'Time Updated' fields in the invoice history? Assuming they are my local times and that it is based on a 24 hour clock with decimals as fractions of the hour (e.g. 15.5 = 3:30 PM)?

  • 0
    SUGGESTED

    As far as i know, the TimeCreated would be when the record is first created in the table, TimeUpdated however could be changed if you were to update the record using Visual Integrator at a later time. For the most part, i would expect the values to be the same or very similar unless you do a lot of updates via Visual Integrator.

    You are correct regarding the way the time is stored. If you divide the value by 24, it becomes a time value that can be formatted to be displayed in any format you prefer.

    Here's an example in Excel.

  • 0

    Time created is time the document was originally created in the entry table..  Time updated is when the record was last written to.  In the case are AR_InvoiceHistoryHeader  that is when either the AR or SO sales journal was run and the invoice was updated from the invoice entry file to  AR_InvoiceHistoryHeader

  • 0 in reply to daburke

    Also, just for fun, here is a SQL statement using the ProvideX ODBC driver to convert the string TimeUpdated and TimeCreated fields to an actual data type of Time and TimeStamp. Substitute table and field names as needed. This SQL contains two columns, one for just the time and another for the date and time together as a timestamp.

    SELECT 
    	CI_Item.ItemCode, 
    	CI_Item.DateUpdated, 
    	CI_Item.TimeUpdated, 
    	{fn Convert(
    	{fn Convert(CI_Item.DateUpdated, SQL_CHAR)} + ' ' + 
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)} + ':' + 
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60), 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)} + ':' + 
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate((((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60) - {fn Truncate((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60), 0)}) * 60), 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)}
    	, SQL_TIMESTAMP)} AS DateTimeUpdatedAsDateTime, 
    	{fn Convert(
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)} + ':' + 
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60), 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)} + ':' + 
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate((((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60) - {fn Truncate((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60), 0)}) * 60), 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)}
    	, SQL_TIME)} AS TimeUpdatedAsTime 
    FROM 
    	CI_Item 

    Here's what this looks like in Crystal Reports.

    If you just need the time in its own field then the following SQL will work.

    SELECT 
    	CI_Item.ItemCode, 
    	CI_Item.DateUpdated, 
    	CI_Item.TimeUpdated, 
    	{fn Convert(
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)} + ':' + 
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60), 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)} + ':' + 
    	{fn Right('00' + {fn Replace({fn Left({fn Convert({fn Truncate((((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60) - {fn Truncate((({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)} - {fn Truncate({fn Convert(CI_Item.TimeUpdated, SQL_NUMERIC)}, 0)}) * 60), 0)}) * 60), 0)}, SQL_CHAR)}, 2)}, '.', '')}, 2)}
    	, SQL_TIME)} AS TimeUpdatedAsTime 
    FROM 
    	CI_Item 

    i have no idea why a built-in function wasn't included to do this, well, at least i haven't figured it out yet.

    Regardless, it is much easier to handle conversion and presentation in the front-end software than through the ODBC driver.

    Here is how you would convert the string TimeUpdated or TimeCreated values into a Time data type in Crystal Reports. This doesn't require any custom SQL statements so you don't have to use the Add Command feature, just select the table and create the formula for the filed you need displayed as a time. Crystal Reports should use your localization settings to determine the default display format for Date, Time, and DateTime data types.

  • 0 in reply to daburke

    from a user interface perspective what is happening? what i am seeing my folks do is type in SO, verify everything, then hit print. how would this translate to created vs updated? does 'print' = posting = updated?

  • +1 in reply to wtcrockett
    verified answer

    Create an SO - does nothing related to invoice data.

    Print an SO - does nothing related to invoice data.

    Create an invoice (possibly from an SO) - sets the Date / Time "created" fields for the invoice.

    Printing the invoice is irrelevant to the dates that appear in invoice history.

    Posting the Sales Journal - moves the data from open invoice tables to AR invoice history and sets the Date Updated.

    Note: some things have changed in recent versions, so to see exactly what happens in your system, go through all the steps in your process, using a test company, and see what the values mean in your version.