Convert number to date in Base (or compare to a date at least)?

[Wildcard]

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.

[doug]

Based in the LO function list and limitations of the old HSQLDB version that LO still is using,consider something like this to display a date in human-readable form:

CONCAT( CONCAT( CONCAT( MONTH( "num" ), '/' ), CONCAT( DAY( "num" ), '/' ) ), YEAR( "num" ) )

There may be other ways too. The HSQLDB version embedded in LO at 4.3.5 still only allows concatation of two strings at a time. That is the reason for all the CONCAT statements.

For date comparison, a little easier: DATEDIFF( 'dd', "date1", "date2" ) See HSQLDB reference

As for the noon to noon issue, sounded like the data is just dates so unclear how time of day would factor. If it is contained in the data, I would just add twelve hours to every date value and use the resulting date, which is the practical effect.

EDIT to fix date comparison reference; thx Wildcard

[Wildcard]

Thanks, the concat thing is sort of helpful.

Datedif only works in Calc, though—I tried it in a Base query and it said it’s not supported.

The dates are strictly speaking timestamps, but for user purposes here I don’t actually care about time, just AM/PM. Regardless, dates and times are stored identically in Calc. It is stored as an integer of the number of days from the hard-coded base date, or with a decimal added for times. 41876 = 28 Aug 14. 41876.75 = 6 PM on that day.

[doug]

Fixed comparison function reference, thx. Also consider TIMESTAMPDIFF from HSQLDB documentation.

[PYS]

Hi
Is it not possible to do the rounding calculation in the spreadsheet? You could filter on this new column …

[Wildcard]

Hi pierre, I just thought I’d mention that what you suggested here (rounding the date in a separate column) is what I ended up doing. I came up with the idea on my own, but thought you’d like to know that yes, that is a good solution.

[PYS]

Hi @Wildcard, nice of you, thank you