# Issues with SQL Date Filtering

**URL:** <https://forum.opensearch.org/t/issues-with-sql-date-filtering/4204>\
**Category:** SQL\
**Created:** [November 15, 2020, 7:42am UTC](https://forum.opensearch.org/t/issues-with-sql-date-filtering/4204 "2020-11-15T07:42:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rc12](https://avatars.discourse-cdn.com/v4/letter/r/e99b99/32.png) [@rc12](https://forum.opensearch.org/u/rc12)\
**Post date:** [November 15, 2020, 7:42am UTC](https://forum.opensearch.org/t/issues-with-sql-date-filtering/4204/1 "2020-11-15T07:42:01Z")

</div>

Hello,  
I am currently trying to use a SQL query to pull 14 days of data from the Elasticsearch instance. I have attempted using many different date calculation for my where clause but they all result in errors.

All of the below are within the WHERE statement

**adddate(@timestamp, INTERVAL 7 day)** with the error _[Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Failed to parse query due to offending symbol [7] at …_

**adddate(@timestamp, INTERVAL ‘7’ day)** with the error _[Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Failed to parse query due to offending symbol [‘7’] at …_

**@timestamp BETWEEN CURDATE()-7 AND CURDATE()** with the error  
_ERROR [42000] [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Failed to parse SqlExpression of type class com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr. expression value: CURDATE() - 7_

**@timestamp \>= DATEADD(day, -7, @timestamp)** with the error _ERROR [42000] [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Function [DATEADD] cannot be found or used here. Did you mean [DATE]?_

**@timestamp \>= CURDATE() - 14** with the error _ERROR [42000] [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Invalid SQL query: Operator [\>=] cannot work with [DATE, INTEGER]_

However, if i set the date in my where clause as _WHERE @timestamp \>= ‘2020-01-07’_ the query works without any issues.

Is there a way for me to query the past 14 days without having to manually change the date every day?

Currently running Elasticsearch 7.8.0 and the ODBC is version 1.11

Thank You

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [November 20, 2020, 4:38pm UTC](https://forum.opensearch.org/t/issues-with-sql-date-filtering/4204/2 "2020-11-20T16:38:52Z")

</div>

Hi @rc12, sorry for the confusion. I guess this is because you didn’t enable the new SQL engine for this date function and interval type support: [GitHub - opendistro-for-elasticsearch/sql: 🔍 Open Distro SQL Plugin](https://github.com/opendistro-for-elasticsearch/sql#experimental). This would be avoided once this new engine released later and enabled by default. Thanks!

---

<div class="post-metadata">

**Author:** ![rc12](https://avatars.discourse-cdn.com/v4/letter/r/e99b99/32.png) [@rc12](https://forum.opensearch.org/u/rc12)\
**Post date:** [November 23, 2020, 5:42pm UTC](https://forum.opensearch.org/t/issues-with-sql-date-filtering/4204/3 "2020-11-23T17:42:13Z")

</div>

Very helpful - thank you for the reply

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [November 23, 2020, 5:54pm UTC](https://forum.opensearch.org/t/issues-with-sql-date-filtering/4204/4 "2020-11-23T17:54:44Z")

</div>

@rc12 Sure, no problem! I’m also updating our README to clarify the docs only available to the new SQL engine: [Support NULLS FIRST/LAST in new engine by dai-chen · Pull Request #843 · opendistro-for-elasticsearch/sql · GitHub](https://github.com/opendistro-for-elasticsearch/sql/pull/843). Thanks!