Reputation: 81
I am using a date function trunc in Oracle, and date_trunc in Postgre which is working fine and matching results. I am trying to use the common single query which should support both Oracle as well as Postgre sql. Can I have some inputs please, With to_char function it is giving some unmatched results.
here is my queries; /Oracle/ select * from emp e inner join dept d on e.deptno=d.deptno and trunc(sysdate)<=trunc(joining_date);
/Postgre sql/ select * from emp e inner join dept d on e.deptno=d.deptno and date_trunc('day', current_date)<=trunc('day',joining_date);
Upvotes: 0
Views: 59
Reputation: 168806
Don't try to have the same code for different RDBMS. They support different functions and you should use the appropriate functions for each RDBMS.
If you must (but I'm sure you don't actually have to) then you can use EXTRACT
in both databases:
select *
from emp e
inner join dept d
on e.deptno=d.deptno
and ( EXTRACT(YEAR FROM current_date) < EXTRACT(YEAR FROM joining_date)
or ( EXTRACT(YEAR FROM current_date) = EXTRACT(YEAR FROM joining_date)
AND EXTRACT(MONTH FROM current_date) < EXTRACT(MONTH FROM joining_date))
or ( EXTRACT(YEAR FROM current_date) = EXTRACT(YEAR FROM joining_date)
AND EXTRACT(MONTH FROM current_date) = EXTRACT(MONTH FROM joining_date)
AND EXTRACT(DAY FROM current_date) <= EXTRACT(DAY FROM joining_date))
)
PostgreSQL fiddle Oracle fiddle
However, that feels like you are trying too hard to get something that works for both databases rather than using the appropriate functions in each individual database.
Upvotes: 2