Solved

Oracle varchar to datetime

Posted on 2006-06-22
10
3,525 Views
Last Modified: 2008-01-09
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
Comment
Question by:tech_question
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16963640
>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
 

Author Comment

by:tech_question
ID: 16963661
default time from sysdate function.
0
 

Author Comment

by:tech_question
ID: 16963675
rather the current time of the system when the transaction is taking place.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 14

Accepted Solution

by:
sathyagiri earned 400 total points
ID: 16963676
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
 

Author Comment

by:tech_question
ID: 16963752
I am getting ora-01821 error date format not recognised.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16963758
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16963763
refer to this when you construct the date format:

http://www.ss64.com/orasyntax/fmt.html
0
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 100 total points
ID: 16963771
how about:

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

Author Comment

by:tech_question
ID: 16963812
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 16963932
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question