The format of default value for datetime field in Informix

This article was published in my csdn.net blog on 26 Dec, 2008.

Recently there was an application involved in Informix database not insert value in a nullable column of the type ‘DATETIME YEAR TO SECOND’, but that column would be used by other applications. Therefore, I decided to use the default scheme provided by database.

It’s very easy to add a constraint of default value for column of type DATE in oracle, just use ‘default sysdate’ in the ‘alter table … modify …’ statement. Unfortunately, when I used the ‘default current’ in the alter table statement, an error ‘Invalid default value for column/variable’ occurred.

After reading the manual of Informix SQL, I found it should be like ‘default current year to second’.