This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma 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


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
Sheriff

Joined: Mar 28, 2008
Posts: 2095
    
  96

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
Sheriff

Joined: Mar 28, 2008
Posts: 2095
    
  96

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
Sheriff

Joined: Mar 28, 2008
Posts: 2095
    
  96

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)
 
Have you checked out Aspose?
 
subject: Date and Time in Where Condition
 
It's not a secret anymore!