using CURRENT_TIMESTAMP in a [CHECK] constraint

DB2 is issuing errors each time I try to create a table with a column defined as follows:

myDate    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK ( myDate < CURRENT_TIMESTAMP),

DB2 says: "SQL0548N  A check constraint that is defined with "CURRENT_TIMESTAMP" is
invalid.  SQLSTATE=42621"

I would like to make sure the user enters a time and date which is before the current time and date.
Please help
VbMonkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
The following works for me:

create table deleteme3 (
  myTS timestamp            
);

> Table DELETEME3 created

alter table deleteme3
add constraint cc1
check (myTS < current timestamp);

> ALTER completed for table DELETEME3

insert into deleteme3      
values(current timestamp - 1 day);

> 1 rows inserted in DELETME3

insert into deleteme3      
values(current timestamp + 1 day);

> INSERT or UPDATE not allowed by CHECK constraint
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VbMonkAuthor Commented:
Thanks daveslash. While I'm checking your solution, can you answer this other bit. I cannot check the length of a char data type. It should be 6 chars long but, an insert statement accepts one with 3 chars or any along as it is below 6. How can i resolve that?
code   CHAR(6) NOT NULL
CHECK (LENGTH(code) > 5 AND LENGTH(code) < 7)
0
LowfatspreadCommented:
davelash's solution is to use Current Timestamp without the underscore?

the length of code will always be 6 since its a fixed length column

you'd need
code varchar(6) not null

to have varying lengths...

do you mean

check(length(strip(code,b,' '))=6)

ie using strip to remove any leading or trailing spaces...
 
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

VbMonkAuthor Commented:
LOWFATSPread, the system accepts a string with less than 4 characters. It should be 4 chars long only. How can I make it accept 4 chars only. I tested with 3 chars and it accepted (which is something I do not want)
0
VbMonkAuthor Commented:
I can't test your suggestions guys, the table cannot be dropped. When i tried to drop the table to implement daveslash's solution it gave this error:
"DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0901N  The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed.  (Reason "bad Defaul".)  
SQLSTATE=58004"
How can i drop the table?
0
LowfatspreadCommented:
you don't need to drop the table

you just need to
use ALTER TABLE
to first drop the existing check constraint on the code

Alter table Tablename drop check constraint constraintname;

then

alter table tablename add check constarint constraintname length(strip(code,b,' '))=6;




   
0
VbMonkAuthor Commented:
i think DB2 does not accept all the statements you are suggesting. I'm getting errors. I don't know the syntax of the statements. If you can help by writing the specific statements (which can recognised by DB2) instead of these generic ones.
0
LowfatspreadCommented:
ok which version of DB2/UDB are you using and which operating system?

sorry my example used strip wihich is a DB2 UDB function on OS/390 v7(?) and above

please specify the table name
and the names your using for any existing constraints...

ok ..
These should work for most DB2 UDBs

you havn't got the time constraint on the table already? so add one with

ALTER TABLE PutYourTableNameHere  ADD CONSTRAINT CK_TIMESTAMP CHECK ( myDate < CURRENT TIMESTAMP);

ALTER TABLE PutYourTableNameHere  ADD CONSTRAINT CK_CODE  CHECK (LENGTH(LTRIM(RTRIM(code)))=6);

i wasn't total clear on wether you wanted a 4 or 6 character code...
but if you don't want to allow spaces in the codes then you should probably have it defined as
a foreign key relationship to a reference table and validate it that way...
hth
0
Dave FordSoftware Developer / Database AdministratorCommented:
> davelash's solution is to use Current Timestamp without the underscore?

In DB2 UDB for iSeries, you can specify Current_Timestamp with or without the underscore. They work equivalently.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.