CODEWITHSUNDEEP
informix
bezz
bezz

Reputation: 83

Informix set string column default value to current date

When creating a table, I would like to set the default value of a char(12) field with todays date. For example, '2016-08-25'

How do I do this in Informix?

Upvotes: 0

Views: 1607

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753990

If you manage to find the documentation on the DEFAULT clause of CREATE TABLE, then you'll see that the options for what can be a default value are quite limited.

Further, there are type-based restrictions on what's allowed:

CREATE TABLE x(y CHAR(12) NOT NULL DEFAULT TODAY);

This generates the semantic error:

SQL -591: Invalid default value for column/variable (y).

That has the expanded meaning:

The specified default value is the wrong type or is too long for a column or an SPL-routine variable.

To specify a valid default value for a column, use the DEFAULT clause in a CREATE TABLE statement. To specify a valid default value for a variable in an SPL routine, use the DEFAULT clause in a DEFINE statement.

Basically, you can't create a default of TODAY for a CHAR-type column.

It will be much simpler to use a DATE column (where you can apply the default of TODAY validly), and when necessary, select that value as a string. There are some minor details like locale and presentation of the data — you might find DATETIME YEAR TO DAY better than DATE because it enforces the ISO 8601-style notation 2016-08-25 for the values.

Upvotes: 3

Related Questions