I don't know if there's a function that will work within the SQL expression, but you can define a calculation field defined as:
GetAsDate ( TheDate )
and select Date as the return type. This will give you a value you can use in a Group By clause in your SQL or in a more convential chart based directly against a found set of records where you sort your records by this field and specify the "grouped" option for your chart.
PS. That ? result IS absolutely maddening. I have found SeedCode's Free SQL Explorer helpful in reducing the frustration level.
Excellent! Creating another field to just have the date using GetAsDate was an idea I hadn't thought of. I was trying to use GetAsDate in the query itself and that didn't work. Thanks for this excellent suggestion. It worked!
I had a couple other formatting issues where FMP wouldn't give me the scale I wanted (vertically) and where the dates along the bottom were totally either wrong or the entire query's dates would appear in a huge list beneath the chart.
To fix these issues, all I ended up having to do was to switch the Data Source's "Chart Data" to use Current Record (Delimited Data) instead of current found set.
Just lots of expermentation required I guess.
With your own SQL queries, you might want to get a Java IDE that also has a database console. I use Intellij IDEA (jetbrains.com) which has a free version and a 30-day eval on the non-free version. Then, you could create a JDBC connection to your FMP solution and just do queries against it (once you enable JDBC in FMP's permissions, of course). It's all relatively easy to set up. On the plus side of this approach is that you can quickly see what your results are **and** get JDBC query error messages (totally lacking in FMP). On the negative side, there's no query builder tool so you have to know SQL. But, hey, nobody knows SQL "perfectly" so everyone fiddles with their queries getting them just right. I find that having this tool *and* FMP is a great combination. WIth IDEA, I can do a query I know works. Then it's just a matter of getting the SQL inside FMP to work. For me, having this remote capability (thanks to the FMP team for creating the remote capability to do queries like this!), is a wonderful sanity check. If there's a better way to do remote queries, I'd like to know about it. I just haven't found anything better or quicker.
Thanks again for your great replies!!!!!!!!!!!