TO_DATE 1

[tmcneil]

All,

I'm sending in a date/time value as a VARCHAR2 to a stored procedure.

InDeparture: 11/29/2007 08:11:00 AM

I need to store this value into a Date field in an Oracle table. I use this line of code to convert the string date/time using TO_DATE.

departure := TO_DATE(InDeparture, 'MM/DD/YYYY HH12:MI:SS AM');

Well, the end result in the table is this.

29-Nov-2007 12:00:00 AM

I'm not sure what I need to do to get the date/time value to be like this.

29-Nov-2007 8:11:00 AM

Any suggestions?

Thanks,
Todd

 

[Dagon]

It works fine for me:

select to_char(to_date('11/29/2007 08:11:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 'MM/DD/YYYY HH12:MI:SS AM') from dual

11/29/2007 08:11:00 AM

It must be something else in your process which is messing up the time.

 

[tmcneil]

Dagon,

I used this:

departure := TO_CHAR(TO_DATE(InDeparture, 'MM/DD/YYYY HH12:MI:SS AM'), 'MM/DD/YYYY HH12:MI:SS AM');

It is still wiping out the time component when the data is stored in oracle.

29-Nov-2007 12:00:00 AM

Maybe it is something else, but I don't know what it is.

Todd

 

[Dagon]

What is departure declared as ?

 

[tmcneil]

It is declared as a VARCHAR2. That could be the problem. I just changed it to a DATE and recompiled the stored procedure. I'll try that.

 

[tmcneil]

Same result.... :-(

There has got to be another way.

 

[Dagon]

All I can say is it works fine for me:

create or replace procedure testdate(InDeparture varchar2) is 
   departure date;
begin
   departure := TO_DATE(InDeparture, 'MM/DD/YYYY HH12:MI:SS AM');
   dbms_output.put_line(to_char(departure, 'MM/DD/YYYY HH12:MI:SS AM'));
end;

set serveroutput on
begin
  testdate('11/29/2007 08:11:00 AM');
end;

11/29/2007 08:11:00 AM

 

[tmcneil]

Got it. I have two stored procedures that are identical, so I was updating the wrong one. it works! I just need to pay more attention to what I'm doing when I get frustrated.

Thanks,
Todd