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!
Printable View
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!
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
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
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.
If you are using PLSQL check out the function 'NVL'.