datetime - Inserting time section in date field using oracle -

i have procedure in oracle:

create or replace  procedure 1     (     p_guid in one.guid%type,     p_start_date in one.start_date%type,     p_end_date in one.end_date%type,     p_return out integer     ) begin     insert onetable (guid, start_date, end_date)   values (p_guid, to_date(p_start_date, 'yyyy-mm-dd hh24:mi:ss'), to_date(p_end_date, 'yyyy-mm-dd hh24:mi:ss'));     commit;   exception         when others         p_return:= 1; end; 

edit, because people seem saying try (this not work either, tried use to_date (above example) try , fix problem):

create or replace  procedure 1     (     p_guid in one.guid%type,     p_start_date in one.start_date%type,     p_end_date in one.end_date%type,     p_return out integer     ) begin     insert onetable (guid, start_date, end_date)   values (p_guid, p_start_date, p_end_date);     commit;   exception         when others         p_return:= 1; end; 

when call c# (with datetime.now example) - fails.

it seems work if start date , end date in format of 05-apr-2013. how time in there in input?

if replace

p_start_date, 'yyyy-mm-dd hh24:mi:ss' 

with

'2013-05-13 12:13:14', 'yyyy-mm-dd hh24:mi:ss' 

this works, upon calling procedure c# fails.

also if manually run procedure db client still have input 05-apr-2013 , works. can not input time fails if try so.

start date , end date date type in oracle db.

--

depending try different messages.

as investigated, seems have this:

select value v$nls_parameters parameter ='nls_date_format'; 

which returns:

dd-mon-rr 

and cannot change not db owner.

--

so how time part in there?

here complete sql:

select parameter, value nls_session_parameters; 

which returns:

nls_language    american nls_territory   america nls_currency    $ nls_iso_currency    america nls_numeric_characters  .  nls_calendar    gregorian nls_date_format dd-mon-rr nls_date_language   american nls_sort    binary nls_time_format hh.mi.ssxff nls_timestamp_format    dd-mon-rr hh.mi.ssxff nls_time_tz_format  hh.mi.ssxff tzr nls_timestamp_tz_format dd-mon-rr hh.mi.ssxff tzr nls_dual_currency   $ nls_comp    binary nls_length_semantics    byte nls_nchar_conv_excp false 

there no need convert function date type. it's date. insert value passed in parameter:

    insert onetable (guid, start_date, end_date)   values (p_guid, p_start_date, p_end_date); 

update

but if variant above don't work , see raised error, stored procedure parameters definition must checked on clients side. can verify p_start_date parameter on client side have datetime type?

update 2

there error on client side. try initialize date parameters in example below:

oracleparameter p1 = new oracleparameter(); p1.dbtype = dbtype.datetime; p1.value = system.datetime.now; 

reason of error c# component treats parameter string , convert string default os format, passes oracle query. oracle trying parse string date type specified in definition of one stored procedure , fails because expects default format nls_date_format parameters.

update 3

real cause of error explained @b0x0rz in comments (wrong return type). anyway it's client-side mistake...