You need to include a time unit, e.g. DAY or MONTH or YEAR TO MONTH. The syntax is
(datetime - datetime) timeunit e.g. (d2 - d1) DAY (d2 - d1) YEAR TO MONTH This has been in Calcite for quite a few releases. > On Jun 11, 2016, at 3:27 AM, ShaoFeng Shi <[email protected]> wrote: > > Julian, from which version Calcite supports the minus operation between two > dates? > > I tried "select cal_dt, (current_date - cal_dt) as diff from kylin_cal_dt" > in Kylin, but get this error: > > Error while executing SQL "select cal_dt, (current_date - cal_dt) as diff > from kylin_cal_dt LIMIT 50000": From line 1, column 17 to line 1, column > 37: Cannot apply '-' to arguments of type '<DATE> - <DATE>'. Supported > form(s): '<NUMERIC> - <NUMERIC>' '<DATETIME_INTERVAL> - > <DATETIME_INTERVAL>' '<DATETIME> - <DATETIME_INTERVAL>' > > Kylin uses Calcite 1.6.0 now; Is an upgrade or type cast needed? Thanks! > > 2016-06-11 6:02 GMT+08:00 Julian Hyde <[email protected]>: > >> In Calcite you can do >> >> (currentdate - cal_date) DAYS >> >> which returns an INTERVAL DAYS value. >> >> In https://issues.apache.org/jira/browse/CALCITE-1124 we added >> TIMESTAMPADD, TIMESTAMPDIFF. >> >> DATEDIFF is mentioned in >> https://issues.apache.org/jira/browse/CALCITE-759 but that has not >> been implemented. >> >> Julian >> >> >> On Fri, Jun 10, 2016 at 7:54 AM, ShaoFeng Shi <[email protected]> >> wrote: >>> I don't see such a function in Calcite (Kylin's SQL parser): >>> https://calcite.apache.org/docs/reference.html#datetime-functions >>> >>> >>> 2016-06-10 10:04 GMT+08:00 Uma Maheshwar Kamuni <[email protected]>: >>> >>>> i want make DateDiff between currentdate and cal_date (which contains >> date) >>>> >>>> ________________________________ >>>> From: ShaoFeng Shi <[email protected]> >>>> Sent: Thursday, June 9, 2016 8:22:08 PM >>>> To: [email protected] >>>> Subject: Re: How to do DateDiff in Kylin >>>> >>>> Hi Uma, could you please give a sample SQL with DateDiff? >>>> >>>> 2016-06-09 13:49 GMT+08:00 Uma Maheshwar Kamuni <[email protected]>: >>>> >>>>> is there any function for DateDiff in Kylin. >>>>> >>>> >>>> >>>> >>>> -- >>>> Best regards, >>>> >>>> Shaofeng Shi >>>> >>> >>> >>> >>> -- >>> Best regards, >>> >>> Shaofeng Shi >> > > > > -- > Best regards, > > Shaofeng Shi
