unidac datediff explanation

[albourgz]

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.

[MaximG]

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:

Code: Select all

UniQuery.SQL.Text := 'Select {fn TRUNCATE({fn CURRENT_DATE}, ''DAY'')} From Dual';

[albourgz]

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!

[AlexP]

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