Datediff

[sboufelj]

Dear all, I a trying to select columns from two tables using a jointure with the condition that the request date in the first table should be in the same day as the collection date in the second table. Here is my sql script to explain more my issue:

select A.*, B.REQUEST_DATE, DATEDIFF(hh,to_char(B.REQUEST_DATE,'DD-MON-YY'), to_char(A.COLLECTION_DATE,'DD-MON-YY')) DIFF_DATE
from V_DATA_OPERATIONAL_LATEST A 
inner join V_PORT_RESET_CANDIDATES B 
on A.line_id=B.line_id 
where DATEDIFF(hh,to_char(B.REQUEST_DATE,'DD-MON-YY'), to_char(A.COLLECTION_DATE,'DD-MON-YY')) < 24 ; 
 

However it doesn't seem to work.
Please help me figure out the issue.
Thank you very much.

[Rabbit]

Microsoft SQL Server doesn't have a to_char() function. Let me know which database you're actually using and I will move your question there.

But here are a few things I don't understand about your post. Why are you converting your date into a string if you're trying to get a DATEDIFF that expects the input to be a date, not a string?

[sboufelj]

thank you for your reply. I am using Oracle data base. and yes you are right, I shouldn't convert to a char but even when I write:

where DATEDIFF('hh', B.REQUEST_DATE, A.COLLECTION_DATE) < 24 ; 
 

it doesn't work.
Thank you in advance.
Regards.

[Rabbit]

Your thread has been moved to the Oracle forum.

You're typing the code tags wrong by the way, it's a forward slash, not a backward slash.

You're going to have to tell us what you mean by it doesn't work. That's very vague. We would need to know exactly what's not working, we need to see sample data and results along with expected results.