# MONTH Interval conversion to use in DATE CLAUSE
**URL:** https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567
**Category:** Kibana
**Tags:** elastic-stack-sql
**Created:** [May 20, 2020, 3:40pm UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567 "2020-05-20T15:40:00Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@vaclav1](https://discuss.elastic.co/u/vaclav1)
#### Post date: [May 20, 2020, 3:40pm UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/1 "2020-05-20T15:40:00Z")
Hello,
I have tried to use following SQL in kibana internal index:
SELECT dashboard.title FROM ".kibana\*" WHERE updated\_at \> TODAY() - INTERVAL CAST(MONTH\_OF\_YEAR(TODAY()) AS interval\_month) MONTHS
Unfortunately I get an error:
[essql] \> Couldn't parse Elasticsearch SQL query. You may need to add double quotes to names containing special characters. Check your query and try again. Error: [parsing\_exception] line 1:79: Invalid [INTERVAL MONTH] value [CAST(MONTH\_OF\_YEAR(TODAY()) AS interval\_month)]: expected digit (at [0]) but found [C]
Tired as well to user CONVERT function and convert to integer, but this does not work either.
Can someone help? :\_)
---
### Author:  [@joshdover](https://discuss.elastic.co/u/joshdover)
#### Post date: [May 21, 2020, 3:39pm UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/2 "2020-05-21T15:39:02Z")
Hi there!
I'm a bit unclear on exactly what you're trying to query for, but you may have more success using the `DATE_TRUNC` function for this query. I _believe_ you're trying to find all dashboards updated since the beginning of the year? If so this query should work:
```auto
SELECT dashboard.title FROM ".kibana*" WHERE updated_at > DATE_TRUNC('year', TODAY())
```
`DATE_TRUNC` will "zero-out" the parts of the date up to the specified unit, in this case the year. So if TODAY() is 2020-05-21T12:45:41, `DATE_TRUNC('year', TODAY())` would equal 2020-01-01T00:00:00.
If that's not what you're trying to get, let me know what you're trying to query for and I can help more 🙂
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [May 21, 2020, 6:06pm UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/3 "2020-05-21T18:06:28Z")
INTERVAL expressions are literals, they cannot be constructed on top fields or scalar functions.
---
### Author:  [@vaclav1](https://discuss.elastic.co/u/vaclav1)
#### Post date: [May 22, 2020, 6:48am UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/4 "2020-05-22T06:48:30Z")
Thank you! Original idea was to get monthly/weekly reports. So I need to use TODAY() or any other function to get 1 of each month and as well current week. I guess I shall be able to use DATE\_TRUNC fucntion like this? DATE\_TRUNC('month', TODAY()), maybe DATE\_TRUNC('week', TODAY())?
Ok got this message, do I need to install or configure it somehow?:
[essql] \> Unexpected error from Elasticsearch: [verification\_exception] Found 1 problem(s) line 1:69: Unknown function [DATE\_TRUNC]
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [May 22, 2020, 1:47pm UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/5 "2020-05-22T13:47:27Z")
DATE\_TRUNC is available since 7.5.0: [https://github.com/elastic/elasticsearch/pull/46473](https://github.com/elastic/elasticsearch/pull/46473)
---
### Author:  [@vaclav1](https://discuss.elastic.co/u/vaclav1)
#### Post date: [May 27, 2020, 10:00am UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/6 "2020-05-27T10:00:17Z")
That will be it! 🙂 and there are probably no other solutions for 6.8?
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [May 28, 2020, 9:49am UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/7 "2020-05-28T09:49:59Z")
> [@joshdover](#):
>
> `SELECT dashboard.title FROM ".kibana*" WHERE updated_at > DATE_TRUNC('year', TODAY())`
Maybe you can do:
```auto
SELECT dashboard.title FROM ".kibana*" WHERE YEAR(updated_at) >= YEAR(TODAY())
```
?
---
### Author:  [@vaclav1](https://discuss.elastic.co/u/vaclav1)
#### Post date: [May 28, 2020, 10:13am UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/8 "2020-05-28T10:13:50Z")
But this compares year or? if there will be two dates in the same year how this is going to work?
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [May 28, 2020, 10:55am UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/9 "2020-05-28T10:55:25Z")
Could you explain what are you trying to achieve?
---
### Author:  [@vaclav1](https://discuss.elastic.co/u/vaclav1)
#### Post date: [June 17, 2020, 4:03pm UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/10 "2020-06-17T16:03:10Z")
I want to filter from particular date until particular date. For example past month.
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [June 23, 2020, 8:13am UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/11 "2020-06-23T08:13:38Z")
Then you can use `MONTH(updated_at) >= MONTH(today()) - 1`
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 21, 2020, 8:13am UTC](https://discuss.elastic.co/t/month-interval-conversion-to-use-in-date-clause/233567/12 "2020-07-21T08:13:55Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.