Value 0 for monthOfYear must be in the range [1,12]

1

Guys,

I’m trying to add a filter like this:

AND "r"."date_authorized" BETWEEN TO_DATE('2019-01-01', 'YYYY-MM-DD') AND TO_DATE('2019-01-31', 'YYYY-MM-DD')

but it return to me a error: Value 0 for monthOfYear must be in the range [1,12]

I’ve tried everything like TO_DATE('20190131', 'YYYYMMDD') or TO_DATE('2019131', 'YYYYMMDD') and it didn’t work also.

Using Dremio 3.1.6-20190307 here.

2

You TO_DATE syntax is fine. What is the type of “r”.“date_authorized”? Is “r” a VDS or a PDS?

3

@ben, just realized that this errors occurs because many of my records are on TIMESTAMP format. It is funny, because this particular statement used to work on the 3.1.0 version. Now that I’ve updated to 3.1.6, it doesn’t. :confused:

4

@allan.sene, if “date_authorized” is a timestamp, your query should still work. For example if:

SELECT DISTINCT TYPEOF(transaction_time) FROM customer_transactions

Returns TIMESTAMPMILLI, then this query will work:

SELECT * FROM customer_transactions WHERE transaction_time BETWEEN TO_DATE('2018-09-01', 'YYYY-MM-DD') AND TO_DATE('2018-09-05', 'YYYY-MM-DD')

Which column is a timestamp in your query?

5

All of those date columns are typed as VARCHAR. Anyway, I’ve managed to make it work using TO_TIMESTAMP function.