Meaningless Drivel is fun!*
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 The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript 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: 959
    
  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: 959
    
  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: 959
    
  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)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Date and Time in Where Condition