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.
ASKER
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:5
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:5
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:
select * from fuel_ticket where location = '0007' and txn_date = to_date('10/7/2011:12:51:5
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:5
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:
it is the AM portion of your string... just use military time maybe?
if you want to use the full query, then you need AM at the end as well
to_date('10/12/2011:01:01:
if you want to use military time then it is:
to_date('10/12/2011:00:01:
to_date('10/12/2011:01:01:
if you want to use military time then it is:
to_date('10/12/2011:00:01:
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
'12-oct-2011 15:25:00'
dont need the to_date function...