With Firebird I have been struggling to get current_date as default for a date field in a table. If I do it in the table design view, I get the value 03/01/-0001 in the field which is not acceptable to the database.
I get it done correctly by entering the command alter table XXXXX alter column YYYY set default current_date.
Looking thru the questions and the bugs, it seems similar things have been reported and complained about.
Is it worthwhile to log (yet another???) bug on this??
Hello,
Setting table field defaults has been a problem for some time. Although this seems to be able to be done through the table design it is not true. See tdf#104375. The report describes how to use an Alter statement to set a default value. This, though, does not work for current date or timestamp.
The easiest method for most databases is to use a trigger. Best to refer to the documentation of the database in use as this can vary from one database to another. Firebird documentation found here → Firebird RDBMS. The 2.5 documentation is the latest available. Version 3.0 used in LO Base.
You do not specify the conditions as to the insertion of the current date. Here is an example trigger:
CREATE TRIGGER clients_date FOR "DefaultDates"
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
new."todays_date" = current_date;
END
This is entered via menu Tools->SQL. The trigger name is clients_date. It will apply the default to both new (INSERT) and existing (UPDATE) records. The trigger is for the table named DefaultDates and the field affected is todays_date.
The trigger can be removed (deleted) with:
DROP TRIGGER clients_date
with clients_date being the name of the trigger to be dropped.
Well, of course I can speak only for myself, but here I have LO6.4.7 with Firebird 3.0 embedded. And what I see is that trying to define current_date as the default value for the data column in the table design view, the result is less than satisfactory. If I give the command “alter table … alter column …set default …” directly in sql to the database, all is OK, except the default value still does not show up in the table design view.
The bug you refer to is still open, so I just added a confirmation that it is still present.