How to Convert a String Date/TIme in Oracle to a Date

I am attempting to query an Oracle database, and I am attempting to use a time in the Where clause that is coming in as a string. I am attempting to use the to_date() function within Oracle, and I was wondering if someone could tell me the correct way on how to use the syntax for writing the query "Select * from A where TXNTime = to_date('10/7/2011 11:15:46',...). I was wondering if someone could tell me the missing syntax from the query to get the time to be able to take AM/PM time as well as be able to take the date in the format mm/dd/yyyy. The time can be in AM/PM and the date will always be formatted as mm/dd/yyyy when it is passed into the query.
thenthorn1010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerrypdCommented:
i think it is a setting in the INI file, but for me the query would look like this:
'12-oct-2011 15:25:00'
dont need the to_date function...
0
thenthorn1010Author Commented:
I have been reading several sites, and I have changed the format of the string a little bit from what is being read in by the database, and I have the following query:

select * from fuel_ticket where location = '0007' and txn_date = to_date('10/7/2011:12:51:55AM', 'mm/dd/yyyy:hh:mi:ss')

After I execute this query, I end up with the error message:

Error starting at line 3 in command:
select * from fuel_ticket where location = '0007' and txn_date = to_date('10/7/2011:12:51:55AM', 'mm/dd/yyyy:hh:mi:ss')
SQL Error: ORA-01830: date format picture ends before converting entire input string
01830. 00000 -  "date format picture ends before converting entire input string"
*Cause:    
*Action:
0
jerrypdCommented:
it is the AM portion of your string... just use military time maybe?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jerrypdCommented:
if you want to use the full query, then you need AM at the end as well
to_date('10/12/2011:01:01:00AM','mm/dd/yyyy:hh:mi:ssAM');
if you want to use military time then it is:
to_date('10/12/2011:00:01:00','mm/dd/yyyy:hh24:mi:ss');
0
thenthorn1010Author Commented:
jerrypd,

The input could have an AM or a PM on the end of it. So, I need to be able to account for both an AM or PM from that data that will be automatically being read in and redisplayed.
0
jerrypdCommented:
i would strongly recommend you use military time then...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ajexpertCommented:
Try This
SELECT   *
  FROM   fuel_ticket
 WHERE   location = '0007'
         AND txn_date =
               TO_DATE ('10/7/2011:12:51:55 AM', 'MM/DD/YYYY:HH:MI:SS AM')

Open in new window

0
jerrypdCommented:
ajexpert - how is that different that what was already discussed?
0
thenthorn1010Author Commented:
jerrypd,

I did switch the application to use military time, per you suggestion...I just was curious as to how you get Oracle to read either AM or PM within the to_date function. Thanks to your suggestion of using military time, I do not have the issue anymore.
0
ajexpertCommented:
Before submitting my comment, I saw author still has some question...

to respond to authors comment# ID: 36958431

Using AM or PM doesn't matter in the date format, you can specify either of the two.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.