Computing date difference from within DevExpress

Question

We use a third-party software that uses DevExpress for building reports. Normally on Oracle - I can get the difference in dates with ease by simply subtracting them:

TRUNC(sysdate) - TRUNC(a.birth_date) AS datediff

I used the same logic from within DevExpress:

CAST(CAST(CURRENT_DATE AS VARCHAR(9)) AS DATE) - CAST(CAST(a.birth_date AS VARCHAR(9)) AS DATE) AS datediff

But doing so gives me this error message:

Invalid cast from 'Decimal' to 'DateTime'. Couldn't store <35> in DATEDIFF Column.
Expected type is DateTime.

How do I fix this?

---

Notes:

• There's a lot of casting going on here but that's just a work-around because TRUNC doesn't work so I'm basically just casting to a VARCHAR essentially removing the time portion and casting it back to a DATE field.
• CURRENT_DATE is the equivalent of SYSDATE in the world of DevExpress.

Answer 1

Looks like the simplest solution is to cast the result to decimal.

CAST(CAST(CAST(CURRENT_DATE AS VARCHAR(9)) AS DATE) - CAST(CAST(a.birth_date AS VARCHAR(9)) AS DATE) AS DECIMAL) AS datediff