Max Date not working as expected
Yes, this is expected behaviour when you are using the non-standard MySQL "feature" of unorthodox GROUP BY. See this related post at SO: Why does MySQL add a feature that conflicts with SQL standards?

Your query basically says to the engine that you want the maximum date and arbitrary values for the other columns. And MySQL does that, exactly. It gets you the maximum date and arbitrary values for the other columns. You should consider yourself lucky that the values came form the same row. They might have been from different rows as well!

Now, to solve your issue, don't use this feature - unless you know very well what you are doing!. Here is another way to get the expected results. Don't use MAX() in the SELECT - which does an implicit GROUP BY the whole set - but do an ORDER BY date DESC and then LIMIT 1 to get only the row with the highest date:

SELECT mod_coli_id, line_number,total_ cost,total_sell, modify_date
FROM mod_customer_order_line_item
WHERE customer_order_id = 17761 
  AND modify_date &lt;= '2016-09-14'
ORDER BY modify_date DESC
LIMIT 1 ;