tech_question
asked on
Oracle varchar to datetime
I have a stored procedure that takes varchar parameter. I convert this to date with the following function, how can I add default time to it.
open_date is a varchar2 , how can I add time to it .
Open_Date = to_date(p_BM_Open_Date, 'mm/dd/yyyy')
eg: instead of 6/21/2006 it should update the table with 6/21/2006 4:05:10 PM
open_date is a varchar2 , how can I add time to it .
Open_Date = to_date(p_BM_Open_Date, 'mm/dd/yyyy')
eg: instead of 6/21/2006 it should update the table with 6/21/2006 4:05:10 PM
ASKER
default time from sysdate function.
ASKER
rather the current time of the system when the transaction is taking place.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am getting ora-01821 error date format not recognised.
how does your p_BM_Open_Date look like?
as angeIII said, you'd use to_date function but it depends on how your pass the date string in?
as angeIII said, you'd use to_date function but it depends on how your pass the date string in?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
actually I messed with something , I removed the h from hh24. I did add the space though after the date.
Thanks JRB1 for pointing it out.
Thanks JRB1 for pointing it out.
I think I amde a typo..
open_date = to_date(p_BM_Open_Date||to
'mm/dd/yyyyh24:mi:ss')
should have been
open_date = to_date(p_BM_Open_Date||to
'mm/dd/yyyyhh24:mi:ss')
open_date = to_date(p_BM_Open_Date||to
'mm/dd/yyyyh24:mi:ss')
should have been
open_date = to_date(p_BM_Open_Date||to
'mm/dd/yyyyhh24:mi:ss')
what time do you want to add to the date?
Open_Date = to_date(p_BM_Open_Date || ' 16:05:10', , 'mm/dd/yyyy hh24:mi:ss')