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





tech_questionAsked:
Who is Participating?
 
sathyagiriConnect With a Mentor Commented:
If your input parameter has a time componenet use

open_date =  to_date(p_BM_Open_Date , 'mm/dd/yyyy hh24:mi:ss')

if it doesn't try this
open_date = to_date(p_BM_Open_Date||to_char(sysdate,'hh24:mi:ss') ,
 'mm/dd/yyyyh24:mi:ss')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>how can I add default time to it.

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')
0
 
tech_questionAuthor Commented:
default time from sysdate function.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tech_questionAuthor Commented:
rather the current time of the system when the transaction is taking place.
0
 
tech_questionAuthor Commented:
I am getting ora-01821 error date format not recognised.
0
 
actonwangCommented:
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?
0
 
actonwangCommented:
refer to this when you construct the date format:

http://www.ss64.com/orasyntax/fmt.html
0
 
jrb1Connect With a Mentor Commented:
how about:

open_date = to_date(p_BM_Open_Date||' ' ||to_char(sysdate,'hh24:mi:ss') ,
 'mm/dd/yyyy hh24:mi:ss')
0
 
tech_questionAuthor Commented:
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.
0
 
sathyagiriCommented:
I think I amde a typo..
open_date = to_date(p_BM_Open_Date||to_char(sysdate,'hh24:mi:ss') ,
 'mm/dd/yyyyh24:mi:ss')

should have been
open_date = to_date(p_BM_Open_Date||to_char(sysdate,'hh24:mi:ss') ,
 'mm/dd/yyyyhh24:mi:ss')

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.