The SQL command “TO_DATE()” returns an error that it’s not supported. (I’m using a Calc spreadsheet as the back end because I just need to run some queries off of it, not actually have any relational database.)
Of course, in the Calc file the dates are displayed as dates, not numbers—but actually they are timestamps and so include fractions in the underlying number storage. This wouldn’t be a problem, but I need to split the day at NOON, and count anything after noon as being the “next day” for purposes of my query.
I thought I could get around this by changing that part of the query to ROUND(“Log”.“DATE”) as “Date”, which at least doesn’t throw an error and does seem to round correctly (i.e. anything PM becomes rounded to the next day). Two problems with this: (1) The rounded date is then shown in the query results as a number, like 41875. (2) I can’t work out how to compare the resulting number to a user defined date parameter.
(1) isn’t a big issue because I can include “Date” in the query twice—once rounded for filter criteria, and the other unaltered for display purposes. (I would have to do this anyways as I’m counting afternoons as part of the next day for filtering purposes, but still need to display the date that was actually entered in the spreadsheet.)
(2) is a problem. I think it would be solved if I can map the rounded date number back to an actual date, but possibly the actual issue is how to ensure the parameter entered by the user is understood as a date and properly converted to a number. (Incidentally, does Base store dates and timestamps the same way as Calc? Such that 28 Aug 14 = 41876?)
I’m giving all the extra data on what I’m actually trying to do, because it’s possible there is a different approach that would be a lot better.
The actual FINAL end result that I need in terms of functionality, is to have the end user fire up Base, click “generate weekly report”, choose the date range (or at least one date—the range will always be from noon Wednesday to the next Wednesday at noon), and then be able to print the query results in a nice tabular form.