Insert NULL value for timestamp

vivek.vivek · #1 (**permalink**) 02-03-09, 08:47

I need to insert a null value for a timestamp column(which is nullable), before submitting a approval request.
I want that colum to be populated only after the request is approved.

Can anyone suggest the format to insert null data?

I tried like this

TIMESTAMP(CAST('' AS VARCHAR(10)),'00:00:00')

But not working

Thanks in advance
Vivek

n_i · #2 (**permalink**) 02-03-09, 09:12

Null values are represented, surprisingly, with the NULL keyword, not with empty strings or zeroes.

rajinbits · #3 (**permalink**) 02-03-09, 11:10

You can use the following:
insert into <tabname> (colname) values (cast (null as timestamp));

stolze · #4 (**permalink**) 02-03-09, 14:31

Yes, you can. But there is no need for the CAST because DB2 is able to figure out the target data type from the column into which you insert.

Btw, it is a common misconception to equate empty strings with NULL. Both are very different things and should not be intermixed.

Peter.Vanroose · #5 (**permalink**) 02-03-09, 17:09

Quote:

Originally Posted by vivek.vivek

I need to insert a null value for a timestamp column(which is nullable)

If the column was declared as nullable but without a default (at CREATE TABLE), just don't insert into that column.
Suppose you have a table with two text columns A and B, and a timestamp column T:

Code:

INSERT INTO mytable(a, b) VALUES ('value for a', 'value for b')

If on the other hand T was declared as "WITH DEFAULT", you'll have to say

Code:

INSERT INTO mytable(a, b, t) VALUES ('value for a', 'value for b', NULL)

camaleonx · #6 (**permalink**) 02-24-09, 06:01

I prove whith CAST(NULL AS CHAR(26)) in INSERT and was good.

My field TimeStap without NULL was:
SELECT CURRENT TIMESTAMP FROM DBA1.TCAMALEON FETCH FIRST 5 ROWS ONLY
--------------------------
2009-02-24-11.43.59.325727
2009-02-24-11.43.59.325727
2009-02-24-11.43.59.325727
2009-02-24-11.43.59.325727
2009-02-24-11.43.59.325727

stolze · #7 (**permalink**) 02-24-09, 08:06

Or you use the following:

Code:

INSERT INTO mytable(a, b, t) VALUES ('value for a', 'value for b', DEFAULT)

Btw, this also works if you have no default specified:

Code:

INSERT INTO mytable(a, b, t) VALUES ('value for a', 'value for b', NULL)

p.s: Some DBMS think that you really want to insert the current timestamp instead of NULL. That's not conforming to the SQL standard and DB2 doesn't do it either.

Internet Services

Web Development

Digital Marketing

Consumer Tech