wood burning stoves 2.0*
The moose likes Java in General and the fly likes Date and Time in Where Condition Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Date and Time in Where Condition" Watch "Date and Time in Where Condition" New topic
Author

Date and Time in Where Condition

Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Hi Guys,

I need to put Date and Time as a Where Comdition in Oracle SQL

Ex:

select * from table where time = '12-Jan-14 11:40:42';


As This is Not working, Error : date format picture ends before converting entire input string.

Please Help Me.


Knowledge enlivens the soul.
Tim Cooke
Bartender

Joined: Mar 28, 2008
Posts: 970
    
  47

What is the Oracle data type for the table field? DATE? TIMESTAMP?


Tim Driven Development
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Tim Cooke wrote:What is the Oracle data type for the table field? DATE? TIMESTAMP?


Date.

I have Tried Myself :

select * from TABLE where (to_char(TIME,'DD-MM-YY HH24:MI')) = '12-02-14 09:17';

Hope this is correct.

Because I can get the OP
Tim Cooke
Bartender

Joined: Mar 28, 2008
Posts: 970
    
  47

You're going to want to use the TO_DATE function


I'm assuming your time is 24 hour format given you do not specify AM or PM. Check the Datetime Format Model for your date string formatting.
arkaes Duraimoni
Greenhorn

Joined: Jan 23, 2013
Posts: 14
try the below one

select * from TABLE where TO_DATE(DATE_COLUMN, 'DD-MM-YY HH24:MI') = TO_DATE('12-02-14 09:17', 'DD-MM-YY HH24:MI');
Tim Cooke
Bartender

Joined: Mar 28, 2008
Posts: 970
    
  47

arkaes Duraimoni wrote:TO_DATE(DATE_COLUMN, 'DD-MM-YY HH24:MI')

Why would you need to call TO_DATE on a column that is already in DATE format?

Edit: Does that even work at all?
Looking at the Oracle Docs for TO_DATE the usage model is

which shows the first parameter as 'char'. The text goes on to clarify that 'char' means "CHAR, VARCHAR2, NCHAR, or NVARCHAR2". So I doubt that a first parameter of DATE will be valid. (Disclaimer: I haven't actually tried it so I'd like to see arkaes or the OP try out these suggestions and report back)
 
Consider Paul's rocket mass heater.
 
subject: Date and Time in Where Condition