Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3549
  • Last Modified:

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





0
tech_question
Asked:
tech_question
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
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
 
tech_questionAuthor Commented:
rather the current time of the system when the transaction is taking place.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sathyagiriCommented:
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
 
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
 
jrb1Commented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now