Base date calculation problem

[GreenThumb]

Hello All. I’m new here and new to Base, so please be a bit patient with me. I am developing a database to help run my family farm. I have created a table for planting which is the problem now. I want to input the date something was planted, and have the database generate the harvest date by adding the # of days it takes a given plant to mature. I keep getting syntax errors. This is the sql code I have tried so far…any help will be appreciated.

SELECT

  "Plants"."PlantName"
, "Plants"."Variety"
, "Planting"."LocationPlanted"
, "Planting"."QtyPlanted"
, "Planting"."DatePlanted"
, "Planting"."TimeToMaturity"
, "DatePlanted" + "TimeToMaturity" AS "Harvest Date" 

FROM “Planting”,

WHERE “Planting”.“PlantID” = “Plants”.“PlantID”
and ‘DatePlanted’ + ‘TimeToMaturity’ >= 'Current_Date

[Wanderer]

As I have not much time start here:

Asking for example code for DATEADD() Function (add or substract dates in SQL)

Hello I think I am doing everything fine, but I am getting an error… don’t know why… I have a DOB (Date of Born) of all my employees in the field “FechaNac” Okay, I want to do something very simple, I want to add to that date, 60 days… so I use the DATEADD() function… What I do is this simple code in HSQLDB… SELECT “FechaNac”, DATEADD( “DAY”, 60, “FechaNac” ) AS “SumaDiasFecha” FROM “tbl_EMPLEADOS” I select the field, then I use the function DATEADD as it is specified in SQL, DATEADD(da…

Above information is for the default HSQLDB. If you use another databese you may simply use DATEADD()

[CRDF]

@GreenThumb

‘DatePlanted’ + ‘TimeToMaturity’ >= ’

And what is your DB?

@GreenThumb

and new to Base

Please take notice that Base is not a DB.
E.g. with Firebird it is

DATEADD("TimeToMaturity" DAY TO "DatePlanted")

TimeToMaturity = INTEGER for days.

[Wanderer]

I also found the use of INTERVAL
DATE_A + INTERVAL '1' MONTH

Add Interval to date in HSQLDB

I have not tried this, and don’t see it in a quick scan of the docs for old HSQLDB, so it may be added later.
https://www.hsqldb.org/doc/1.8/guide/guide.html

[CRDF]

By the way…

@GreenThumb

I am developing a database to help run my family farm.

So first of all: what kind of DB are you intending to use?
As it is a DB for real (“production”) work, forget about embedded mode.
Use external, either Firebird or HSQLDB 2.7.

[CRDF]

FROM “Planting”, “Plants”
WHERE […]

[Villeroy]

If your database happens to be an embedded HSQLDB, the status bar of your Base window looks like this:

Embedded HSQL does not include a dateadd function.
Possible solutions:

• You may upgrade to a more recent version of HSQL with dateadd function. This can’t be an embedded database. The database will reside separate files outside the Base document. This has many advantages.
• You may switch to embedded Firebird or better external Firebird (one file).
• You may use a completely different external database, such as MySQL.
• There is a workaround for embedded HSQL:
  SELECT DATEDIFF('day', '1899-12-30', "DatePlanted") + "TimeToMaturity" AS "Harvest Date"
  “TimeToMaturity” should be an integer number of days.
  DATEDIFF calculates an integer day number. A form’s or report’s formatted control can be formatted to “translate” these integer numbers back into correct dates. Importing such day numbers into Writer tables or Calc works in the same way. Just apply some date format.

[RobertG]

The function DATEADD isn’t available for internal HSQLDB. Have a look at the opened Base file. At the bottom in status bar you could see the used database. Default is “Embedded HSQLDB”.
.
You could create an embedded FIREBIRD database when switching experimental functions to “On”. Then “Firebird” will be available for creating. If you have created such a Base file you could switch experimental features “Off” again.
.
You could connect to an existing database and choose “Firebird”. The file for the Firebird-Database would be created if it doesn’t exist.
.
Firebird will support DATEADD and also adding days to a date directly like
"DatePlanted" + 30

[skyandrews]

A little different approach:

• Get difference between plantDate and lastDate… or Now()… in days using DateDiff().
• Compare growDays (difference) to 60 day maturity.
• SQL Design: Column1 DateDiff ( ‘dd’, “plantDate”, Now()) as growDays - Note: Use single quote marks around the dd property! - Column2 Case When “growDays”>=60 Then ‘Mature’ Else ‘Not Yet’ End