Sage 300 stores dates as numeric values in the format YYYYMMDD. For example, June 30, 2009 (06/30/2009) is stored as 20090630. This makes it difficult to work with date fields, because:

• Most people expect dates to be displayed in the "usual" date format, such as MM/DD/YYYY or DD/MM/YYYY.

• Doing date math on numeric values is more difficult. For example, adding 15 days to 06/30/2009 gives 07/15/2009, but adding 15 to 20090630 gives 20090645, which isn't a valid date.

To make it easier for you, Stonefield Query automatically converts Sage 300 dates to real dates when it displays them on reports, and vice versa when you filter a report on a date. However, because of this automatic conversion, using Sage 300 date fields in expressions in Stonefield Query means using a built-in function to convert the date properly.

For dynamic expressions in filter conditions, use the AccpacDate function to convert a real date to a Sage 300 date. For example, suppose you want a list of invoices for last week. For that, you'd create a filter condition on Invoice date is between and choose Expression for the Compare To type. DATE() - 7 is an expression that gives seven days ago and DATE() - 1 gives yesterday's date, so you might expect to use those for the two date expressions. However, you can't use those expressions as is because Stonefield Query will try to compare those date values to the numeric values Sage 300 stores, which won't work. Instead, use AccpacDate(DATE() - 7) and AccpacDate(DATE() - 1) to convert those date values to Sage 300 date values.

For formulas, use the AccpacDateToRealDate function to convert an Sage 300 date to a real date. For example, suppose you want to create a formula that you can use for invoice aging. For AR invoices, the 31 to 60 day amount is the Amount Due field (AROBL.AMTDUEHC) if the Due Date (AROBL.DATEDUE) is between 31 and 60 days ago or 0 if it's newer or older than that, so you might think you'd use this formula:

iif(between(date() - AROBL.DATEDUE, 31, 60), AROBL.AMTDUEHC, 0)

However, because DUEDATE is a numeric value, you can't subtract it from the current date. Instead, use AccpacDateToRealDate(AROBL.DATEDUE) to convert it to a real date. In that case, the formula is:

iif(between(date() - AccpacDateToRealDate(AROBL.DATEDUE), 31, 60), 
    AROBL.AMTDUEHC, 0)