Re: [Q] Getting default value into DATETIME column

wyang@aerotek.com (wyang) wrote: :titan wrote: :} :} Environment: RS6k F40, AIX 4.1.5, Informix SE 7.1 using Db-access. :} :} Hi, :} :} Can anyone suggest why the following code doesn't work as expected ? The :} default value (as I see it) should contain the exact time (to the second :} anyway) but actually shows 00:00. I've checked the FAQs and _have_ RTFM and :} have found nothing to suggest that it should fail. The code was not :} executed at midnight, BTW :-) :} :} <input> :} create table activitylog :} ( :} actid serial, :} acttask int, :} acttime datetime year to second default current year to second, :} actstaff int :} ); :} insert into activitylog(acttask,actstaff) values(19,1); :} select * from activitylog; :} <output> :} actid actstaff acttime acttask :} :} 1 1 1997-02-07 00:00:00 19 :} :} TIA :} :} Ian Bishrey : :Hi, Ian : :I think this is related to your default 'CURRENT'. 'Current' function :will be excuted only once, that is, when the table was created. This is *not* the case. current is here a part of the default clause, which is evaluated when an insert statement is executed, not when the table is created. : It will :always uses the value when the table is created. I remember there are :some discussion about using 'current' in stored procedure, you can't use :it to create a time stamp. You can, but must remember that current is evaluated just once for each execution of the stored procedure (again not when it is created). If you use current several times in the same stored procedure it will return the same value. Strange if you ask me, but that's the way it is documented and works. : :Look at 'Informix Guide to SQL' Reference Dec. 1991 pp 7-363. This may :help you. I don't have exactly this version of the manual, but in a later issue it is stated that current as part of an sql statement is executed once when that sqlstatement starts. I can understand that this statement, with some creative thinking, may be understod to also be the case for current in the default part of a create table statement. This, however, isn't the case. :The solution is not using default in your table design. Instead you can :define the column as: : :acctime datetime year to second; : :and in your sql statement, say 'insert into activitylog(acttask, :acctime, actstaff) values(19, CURRENT, 1)' This works of course, but luckily isn't necessary. It may however possibly solve the problem Ian Bishrey reported although I doubt it. Check Jonathan Leffler's post in this thread for the answer to the problem. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org