DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Dates and Nulls

  1. #1
    Join Date
    Feb 2000
    Posts
    24
    Hello!

    I'm writing an insert statement into a date field. But there is the possibility that the date would be sent over as null.. everytime i convert to a date for the insert on a null I get an error.. Is there a syntax around this?

    Thanks!

  2. #2
    Join Date
    Jan 2000
    Location
    Baton Rouge, LA, US
    Posts
    6
    Following syntax will work:

    insert into mytable values ( to_date(NULL, 'mm-dd-yy'))
    OR
    insert into mytable values ( to_date('11-12-00', 'mm-dd-yy'))

    Hope this will help

    dk

  3. #3
    Join Date
    Feb 2000
    Posts
    24
    The only problem is that it is the same insert statement, so it works when it's a date, but if it's not, it puts 'tick marks' around the word NULL and generates an error:

    value=NULL
    to_date('value', 'mm/dd/yyyy') = error

    value=01/02/2001
    to_date('value', 'mm/dd/yyyy') = works

  4. #4
    Join Date
    Aug 2000
    Posts
    163

    is it really null?

    Szhark,
    Are you sure your incoming value is Null? My experiece working with flat files and other input is saying very often it is an empty string.
    If it was null it would work. This is my example:

    create table mytable (test_date date)
    /
    Table created.
    1 insert into mytable
    2* select to_date(null, 'mmddyyyy') from dual
    SQL> /
    1 row created.
    SQL> insert into mytable
    2 select null from dual;
    1 row created.
    There is something else but Null creating your problem.

  5. #5
    Join Date
    Jan 2001
    Posts
    36
    If you are using PLSQL check out the function 'NVL'.

Posting Permissions

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


Click Here to Expand Forum to Full Width