Devart Forums

Hi,

how can I writhe the following oracle statement using unidac (datediff or other statement) without any db-specific sql: (thedate is a timestamp)
SELECT TRUNC(theDATE) as closedate, xxx
FROM theTable
WHERE TRUNC(theDate)=TRUNC(SYSDATE-1)
GROUP BY TRUNC(theDate), xxx
ORDER BY TRUNC(theDate), xxx

Kind regards.

To use Oracle TRUNC and SYSDATE functions when writing SQL queries, that don't depend on the used database specificity, you can try to apply SQL functions TRUNCATE and CURRENT_DATE correspondingly: https://www.devart.com/unidac/docs/?unisql.htm

For example:

ok,

First, it doesn't always work: trunc(date1-date2,'DAY') does not work although trunc(date1,'DAY') and trunc(date1-date2) work.

{fn TRUNCATE(thedate,'')} seems to do the same than trunc(thedate) in oracle. {fn TRUNCATE(date1-date2,'')} also works in oracle. But what about postgres?

1. truncate is in numeric functions. Do you have a full list of the items that can be used (in your example, there is 'DAY') ?
2. How do I remove 5 days (e.g. sysdate -5)? Can you give examples/specifications of use of datediff?


It would be nice to add more examples to the documentation . Cheap and less threads in forum!

1) trunc(date1-date2,'DAY') such construct is not supported by Oracle

2) In PostreSQL, such constructs are not supports at the moment

3) You can find the full list of parameters at https://docs.oracle.com/cd/B19306_01/se ... m#i1002084

4) To add/delete days, hours, minutes, etc., you can use the following SQL:
select {fn DATEADD(day,SYSDATE,-5) } from dual