Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    27

    Question Unanswered: Insert NULL value for timestamp

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Null values are represented, surprisingly, with the NULL keyword, not with empty strings or zeroes.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2007
    Posts
    27
    You can use the following:
    insert into <tabname> (colname) values (cast (null as timestamp));

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Feb 2009
    Posts
    1
    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
    Last edited by camaleonx; 02-24-09 at 07:08.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jul 2013
    Posts
    1

    Yes it does

    Quote Originally Posted by stolze View Post
    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.
    Before DB2 9.7 empty string could be inserted as a null value without extra coding like insert into table1 values (1,"")

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by agus1985 View Post
    Before DB2 9.7 empty string could be inserted as a null value without extra coding like insert into table1 values (1,"")
    Let alone the fact that you have joined a discussion that ended 4 years ago, you got it all wrong. Only starting with DB2 9.7, and only in Oracle compatibility mode, DB2 will deviate from the SQL standard and, like Oracle, will treat empty strings as NULLs.

    Besides, string literals in SQL are surrounded with single quotes, not double quotes as in your "example".
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •