Asking for example code in SQL with NOW() function

[mrmister]

Hello

Please, could you give me an example in SQL to create a table with just one colum, for example: Current_Date and introduce into that column the actual systme date?

I’d like to see an example of how the NOW() function from SQL can be implemented into Base

Cheers

[doug]

The CREATE TABLE statement uses a slightly different syntax as documented in HSQLDB. You will need to execute the following in Tools → SQL:

CREATE TABLE TableA (
  Field1 INT PRIMARY KEY,
  Field2 DATE DEFAULT CURRENT_DATE
)

The above SQL query will create a permanent table that you can use to store new data, and the default value for Field2 in all new rows will be the current date at the time the row is created. This is not the usual way to create tables in Base.

On the other hand, here is a SELECT query that uses the NOW() function to display a new column alongside the columns of the TableA above:

SELECT "FIELD1", "FIELD2", NOW( ) "func_now" 
FROM "TABLEA"

The output is as follows (with a little sample data in TableA):

Unlike the CREATE statement, this SELECT statement creates only a temporary, unstored output. The SELECT statement can be run through the Base query builder (View → Switch Design View On/Off)

(if this addressed your question, please accept the answer)

[mrmister]

Hi Doug, thanks a lot for that example.
Just a question…
Do you know any place where I can find a complete documentation about the SQL used by LibreOffice Base?
I’m consulting a website… w3schools.com but the SQL there it’s different from the SQL used by LibreOffice Base…
I’m trying to find a place where I can find the structure of the SQL language used by LibreOffice Base with examples of code for every command of function…
Do you know any place describing the whole reference?
Cheers

[doug]

I think the main difference is that optional backquotes (`` - the special key that is shift - ~ ), which you can use in other SQL implementations, you must use in Base/HSQLDB. I use the MySQL documentation for the most part. The current verision of HSQLDB is similar, most incompatibilities are from the fact that Base is using the older HSQLDB v. 1.8. In Base, double quotes ("") are used interchangeably with backquotes, not sure that is typical in SQL.

[mrmister]

Hi doug, thanks for your very nice reply and the examples… However I see LibreOffice Base not only accept SQL commands (HSQLDB 1.8) but also MySQL commands as well…
I’ve used the MySQL function to retrieve the system date CURDATE() and it works! so I am a bit confusing about LibreOffice Base… is it using SQL, HSQLDB and also MySQL ? Base use Java also? just wondering. I’d like to hear your thoughts.

[mrmister]

This is my piece of code:
SELECT “Nombre”,
“Apellido1ero”,
“Apellido2seg”,
“Sexo”,
“FechaNac”,

CURDATE( ) “FechaActual”

FROM “tbl_EMPLEADOS”

[doug]

I believe Base relies on the SQL interpreter for whatever database it is connected to. If it is connected to a MySQL database, MySQL syntax will work (for the most part, with any limitations imposed by connectors). If it is relying on the native HSQLDB engine, that will control. The syntaxes are not totally interchangeable, but they are closer together than, say, Microsoft SQL syntax, or MS Access syntax, which tend to be different. Functions can be different, like CONCAT().