CODEWITHSUNDEEP
postgresqloracle-database
Michael
Michael

Reputation: 81

Need a common date format function which support Oracle and Postgre sql

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

Answers (1)

MT0
MT0

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

Related Questions