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

[mrmister]

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(datepart,number,date).
Datepart could be year, month, day, etc…
Then the quantity to add…
Then the datefield that is involved in the calculation, in this case we are adding 60 days to the Date of Born of the employees, so “FechaNac”

And then I want to present the results with the Alias “SumaDiasFecha” translated to english would be “AddDaysToDate”
Okay, I get an error that says “Access denied”…

???

SQL Status: S1000

Error code: -33

Access is denied: DATEADD in statement [SELECT “FechaNac”, DATEADD( ‘day’, 10, “FechaNac” ) “SumaDiasFecha” FROM “tbl_EMPLEADOS”]

How may I solve this?
Could you give me please an example code that be working?
Cheers

[doug]

I think the only moderately simple answer is to follow oweng’s link and use a split HSQLDB server running a current version, as v.1.8 will not do this. I theory, it could be possible to work in reverse and make a table of all dates and intervals, use the DATEDIFF function to work backword to find the right entry, but that would be complex and time-consuming. May be collateral benefits to running external HSQLDB server…

[PYS]

Hi - You can use an SQL statement like this to create a view:

SELECT "IdTable", "Date", "NbJours", DATEDIFF( 'DD', '1899-12-30', "Date" ) + "NbJours" "Added" FROM "Table" AS "Table"

The idea is to calculate the number of days between the date and the “origin” and add the number of days. This gives us of course a “serial number” but simply executes the view then right click on the header then Column Format and format it as dates. In doing so, the format is stored (which is not the case with a query, which is why I suggested the creation of a view). See the AddDays.odb example.

The date “origin” is the same as that used by the spreadsheet: help

[EDIT]

Apart from the ability to save the column format, thanks to a view, the “serial number” can be used without further SQL in a form or report.

The new version of the AddDays.odb database example shows how to do without additional SQL function. Besides, I do not think there is a SQL function (string, date, whatever…) allowing this with HSQLDB version used by LibreOffice.

For a report, that is simple, just found it on the same SQL statement and select the field format.

For a form the “start” date is unfortunately different (I have not investigated whether the bug was already found): Forms use 1900-01-01.

So we have to use a different query and use it as datasource in the form. Then simply display the field in a Date control that will display the number as a date.

[doug]

Yes, this is creative, especially the formatting of the table view. Do not see a function to make the same transformation in the data as does the format option, which is purely aesthetic. The CAST function does not work on the indexed field. To make it work all the way, you would need to have a second table matching the index with an actual date.

[mrmister]

Hi doug, sadly HSQLDB 1.8 does not support the DATEADD function I’ve read that in an OpenOffice forum

Hi pierre, your solution it’s quite interesting… it works fine, however, force us to reformat the field, and even if we save the query, the field format is not remembered, so everytime we run the query, we lose the format and we have to reformat again the field as date.

Okay.

Let’s focus right now in that serial.

What that serial mean exactly?

For example:

25/06/08

We add it 20 days.

And we get this serial:

39644

Okay.

What 39644 means exactly?

When we format it as date, we get the proper date with the sum: 15/07/08

So 39644 ---- means —> 15/07/08

Fine.

I think we can solve this date add problem understanding that serial, and converting it to a text field… perhaps using text functions? to decode 39644 into text we can post as an alias?

Just some thoughts.

What do you think?

Cheers

[PYS]

Hi @mrmister

As in my example, I did not say create a query but create a View. The views store format change unlike queries. To create a view:

1. View > Database Objects > Tables
2. Create View

39644 is number of days between the date that serves as a starting point for LibO and the date compared. In other words it took 39644 days between 30/12/1899 and 25/6/8. Dates are managed as numbers. >0 are the dates after the date “origin”, <0 are the earlier dates.

[mrmister]

Hi pierre, very interesting. What do you think about getting those days and translating them into SQL ?
Do you think we could use string functions and showing an alias in a kind of date format, retrieved from that number, for example 39644… do you think it could be possible to translate that into date format using SQL ?

[PYS]

See my first answer: I completed to incorporate all the information

[mrmister]

Yes pierre you are right, if you save a TableView it remember the format, hence you can proceed with the calculation displying correctly the result