Oracle: filter query on datetime

Question

I need to restrict a query with a

SELECT ... FROM ... 
WHERE my_date=(RESULT FROM A SELECT)
...  ;

in order to achieve that I am using as result of the select a timestamp (if I instead use a datetime I get nothing from my select probably because the format I am using trims the datetime at the second).

Sadly this is not working because these kindo of queries:

select DISTINCT TO_DATE(TO_TIMESTAMP(TO_DATE('25-10-2017 00:00', 'dd-MM-yyyy HH24:MI'))) from DUAL;

return an

ORA-01830: date format picture ends before converting entire input string

how to deal with timestamp to date conversion?

What is wrong with CAST(timestamp_col AS DATE)? Can you post the full query? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 13, 2017 at 10:58
Thank you Tim, now I got a datetime with the cast( ...as date). So you answered the question. Still I didn't solve my problem which is another question. In fact: SELECT * FROM customer WHERE last_update_dt = CAST( (select DISTINCT TO_TIMESTAMP(last_update_dt) from ... ) AS DATE); is returning nothing as if testing the equality between dates were a wrong practice. — jimifiki
– jimifiki, Commented Dec 13, 2017 at 11:19

arunb2w · Accepted Answer · 2017-12-13 11:45:57Z

1

If you want to just compare and check only he dates use trunc on both LHS and RHS.

SELECT ... FROM ... 
WHERE trunc(my_date)=(select trunc(RESULT) FROM A)
...  ;

This will just compare the dates by truncating the timestamp values

answered Dec 13, 2017 at 11:45

arunb2w

1,1961 gold badge11 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ruvani Jayaweera · Accepted Answer · 2017-12-13 13:14:22Z

1

You can use the combination of "TRUNC" and "IN" keywords in your query to achieve what you are expecting. Please check the below query sample as a reference.

SELECT * FROM customer WHERE TRUNC(last_update_dt) IN (select DISTINCT (TRUNC(last_update_dt)) from ... )

Cheers !!

answered Dec 13, 2017 at 13:14

Ruvani Jayaweera

363 bronze badges

1 Comment

jimifiki Over a year ago

Thanks for the answer. In my specific case the equality is enough so I went for an equality check. Your answer has been of help for me.

Collectives™ on Stack Overflow

Oracle: filter query on datetime

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related