• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1139
  • Last Modified:

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.
0
thenthorn1010
Asked:
thenthorn1010
  • 5
  • 3
  • 2
3 Solutions
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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