Devart Forums

Posted: **Thu 17 Nov 2016 16:19**

I have 2 macros:
{Currentdate}="SYSDATE" in oracle, "localtimestamp" in postgres.
{fromdual}="From dual" in oracle, "" in postgres.

=> query
select {fn TRUNCATE({currentdate},'DDD') } {fromdual}

=> Works fine in oracle, not in postgres. In postgres, using db monitor, I see that truncate is mapped to trunc function that (according to postgres documentation) can not be used on datetime (date_trunc should be used in postgres).

I need a way to truncate any timestamp to the date at 0h00, that would work with (at least) oracle and postgres. Any hint?

Thank you

Posted: **Fri 18 Nov 2016 09:39**

UniDAC Unified function TRUNCATE is used for Number data types, therefore, it's incorrect to use it for the DATE and TIME types. More details about Unified SQL in our online-documentation: https://www.devart.com/unidac/docs/?unisql.htm .

To truncate days in the date value, use UniDAC Conditional Execution (IF) instead of SQL Functions. For example, if you use trunc(SYSDATE, 'DD') call in Oracle and date_trunc('day', localtimestamp) call in PostgreSQL, to get a date value where days are truncated, then SQL query for TUniQuery.SQL.Text can look like this:

Code: Select all

select 
  {if Oracle} trunc(SYSDATE, 'DD') {endif} 
  {if PostgreSQL} date_trunc('day', localtimestamp) {endif} 
  as date_trunc_day 
...

Devart Forums

TRUNCATE does not work for all databases

TRUNCATE does not work for all databases

Re: TRUNCATE does not work for all databases