Ask Your Question
0

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

asked 2015-02-01 03:12:56 +0100

Wildcard gravatar image

The SQL command "TO_DATE(<number>)" 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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-02-01 05:30:12 +0100

doug gravatar image

updated 2015-02-09 03:12:24 +0100

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

edit flag offensive delete link more

Comments

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.

Wildcard gravatar imageWildcard ( 2015-02-01 07:00:49 +0100 )edit

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

doug gravatar imagedoug ( 2015-02-09 03:13:41 +0100 )edit

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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-02-10 16:01:26 +0100 )edit

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. :)

Wildcard gravatar imageWildcard ( 2015-03-09 09:24:05 +0100 )edit

Hi @Wildcard, nice of you, thank you :)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-03-09 09:37:16 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-02-01 03:12:56 +0100

Seen: 1,199 times

Last updated: Feb 09 '15