Hi! I'm trying to assign a DEFAULT to a DATETIME values of something other than CURRENT, and can't figure out what I am doing wrong.

First of all, I have the following environment variable set:

GL_DATETIME=%Y%m%d %T.%F3

so my value for right now, for instance would be

20021219 19:17:38.456

if I had stamped a record with current and then queried it.

I'm trying to set up a table with DEFAULT values like this:

CREATE TABLE t_system_config (
config_type_id INTEGER NOT NULL REFERENCES t_config_type(config_type_id)
CONSTRAINT fk_system_config1,
dt_eff_end DATETIME YEAR TO FRACTION
DEFAULT '20991231 23:59:59.999' NOT NULL,
dt_eff_begin DATETIME YEAR TO FRACTION
DEFAULT '20010101 00:00:00.001' NOT NULL,
cfg_type_char1 NCHAR(10),
cfg_type_int1 INTEGER,
cfg_type_double1 FLOAT,
dt_last_update DATETIME YEAR TO FRACTION DEFAULT CURRENT NOT NULL,
PRIMARY KEY (config_type_id, dt_eff_end) CONSTRAINT pk_system_config
);

I have tried single quotes, parens, and both in combination (started with none of the above)

and what I get is:

CREATE TABLE t_system_config (
config_type_id INTEGER NOT NULL REFERENCES t_config_type(config_type_id)
CONSTRAINT fk_system_config1,
dt_eff_end DATETIME YEAR TO FRACTION
DEFAULT '20991231 23:59:59.999' NOT NULL,
# ^
# 591: Invalid default value for column/variable (dt_eff_end).
#
dt_eff_begin DATETIME YEAR TO FRACTION
DEFAULT '20010101 00:00:00.001' NOT NULL,
cfg_type_char1 NCHAR(10),
cfg_type_int1 INTEGER,
cfg_type_double1 FLOAT,
dt_last_update DATETIME YEAR TO FRACTION DEFAULT CURRENT NOT NULL,
PRIMARY KEY (config_type_id, dt_eff_end) CONSTRAINT pk_system_config
);

Has anyone done this successfully? There has to be some kind of trick, but none of the manuals nor the Informix Handbook has helped. I have setup no hidden constraints on DATETIME values in the database, either.

Please help me if you know to solve this one. Thanks!

OK, folks,

I figured it out myself through trial and error. Since this was a large Grief Coefficient problem for me, I decided to post the solution for any of you who come later and have this problem.

A quick word of advice: ignore the manuals and books. They LIE to you, saying things like "literal values must be enclosed in quotes". I went through every one of the manuals as well as The Informix Handbook, and still had to work through it solo last night. The error messages from the database aren't very helpful, either. While none of the print material will tell you this, there is very little flexibility and no guidance whatsoever in terms of where you place the "NO NULLS" phrase, so don't get creative in hopes of improving style or readability.

I'm running Informix SE 7.25 on an IBM x342 (dual 1.4 GHz Pentium 3) eServer with Red Hat 7.2.

Here's the code that finally worked:

CREATE TABLE t_system_config (
config_type_id INTEGER NOT NULL REFERENCES t_config_type(config_type_id)
CONSTRAINT fk_system_config1,
dt_eff_end DATETIME YEAR TO FRACTION
DEFAULT DATETIME(2100-12-31 23:59:59.999) YEAR TO FRACTION NOT NULL,
dt_eff_begin DATETIME YEAR TO FRACTION
DEFAULT DATETIME(2001-01-01 00:00:00.000) YEAR TO FRACTION NOT NULL,
cfg_type_char1 NCHAR(10),
cfg_type_int1 INTEGER,
cfg_type_double1 FLOAT,
dt_last_update DATETIME YEAR TO FRACTION DEFAULT CURRENT NOT NULL,
PRIMARY KEY (config_type_id, dt_eff_end) CONSTRAINT pk_system_config
);

I hope this makes someone's day- it sure would have made mine. Have a happy and safe holiday season. :-)