DATEADD (or similar) in calculated field in view (embedded db)

[trueriver]

hi

I want a drop down to select one of the eight days from one week ago till today.

So I created a table with the integers 0…7 in column N, and tried to create a view with the calculated column subracting N from today’s date. I have used today() - N AS WKDATE to do the similar in Ms Access.

Is this possible in base with the embedded db? I cant find a syntax that the view builder will accept.

If this technique is not possible, any suggestion about how I would create such a drop down box that automatically shows today’s date and the previous seven dates?

[Ratslinger]

Hello,

Date math in HSQLDB embedded is difficult at best. You could try this method by @PYS in this post → How to do basic arithmatic with dates in Libre Base queries using current_date. Have just tested and works but you must then also account for changeover in months.

My preferred solution would be to change DB’s to at least a split DB with a newer HSQL thus allowing better date functions. Again, Firebird may be even better since you can simply do:

Select current_date -1 from "Table_name"

and use simple +/- calculation on the date.

[trueriver]

Thanks for this answer, depressing though it is…

I agree with your preferred solution as the most professional solution.

For now, though, I will go for keeping dates as separate fields for month and day