Results 1 to 4 of 4

Thread: NULL date value

  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: NULL date value

    I am trying to insert value into a table by selecting from another table.
    say into table1 from table2
    table1-field1,field2,field3(nullable and type date),field4
    table2-fld1,fld2
    If I give

    Insert into table1
    select fld1,'abc',NULL,fld2
    from table2

    I get an error NULL is not a field in the inserted or selected table.

    If I give

    Insert into table1
    select fld1,'abc','',fld2
    from table2

    It gives invalid date format error message.

    Could someone please tell me how I can insert Null value to date field by using select statement??????????

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: NULL date value

    Originally posted by angelc
    I am trying to insert value into a table by selecting from another table.
    say into table1 from table2
    table1-field1,field2,field3(nullable and type date),field4
    table2-fld1,fld2
    If I give

    Insert into table1
    select fld1,'abc',NULL,fld2
    from table2

    I get an error NULL is not a field in the inserted or selected table.

    If I give

    Insert into table1
    select fld1,'abc','',fld2
    from table2

    It gives invalid date format error message.

    Could someone please tell me how I can insert Null value to date field by using select statement??????????
    INSERT INTO TABLE1 (FIELD1, FIELD2, FIELD4)
    SELECT FLD1, 'abc', FLD2 FROM TABLE2
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    In DB2 V7 there is the function NULLIF, which returns NULL, if both arguments are the same, i.e NULLIF(CURRENT DATE, CURRENT DATE) results in NULL.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use
    cast(null as date)
    in place of your null in the original post ..

    But n_i 's suggestion is the best

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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