• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Date and Time in Where Condition

 
Shahir Deo
Ranch Hand
Posts: 80
Hibernate Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Tim Cooke
Sheriff
Pie
Posts: 3056
126
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the Oracle data type for the table field? DATE? TIMESTAMP?
 
Shahir Deo
Ranch Hand
Posts: 80
Hibernate Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3056
126
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3056
126
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic