This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android 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: 78

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: 832
    
  38

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


Tim Driven Development
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 78

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: 832
    
  38

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: 8
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: 832
    
  38

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
 
Similar Threads
Date problem
advanced calculator
Date difference between Java and db2
experiation once again
Java Swing Date & Time Picker Component