You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Ns G <ns...@gmail.com> on 2015/12/23 10:12:06 UTC

REG: using between for dates

Hi Team,

I want to fetch records with  date column between current date and a past
date.
Currently when i use between in phoenix it is not working.

Can any one help?

Query:

SELECT id,to_date(SUBSTR(ID2, (LENGTH(ID2)-7), 8),'yyyyMMdd','GMT') FROM
table1 h WHERE to_date(SUBSTR(ID2, (LENGTH(ID2)-7), 8),'yyyyMMdd','GMT')
between current_date() and (current_date()-2000)

RESULT:

 Empty result set fetched


Thanks,
Ns

PS: Valid data exists in the table to satisfy the query.

Re: REG: using between for dates

Posted by Nick Dimiduk <nd...@gmail.com>.
What version of Phoenix are you using? You're probably hitting PHOENIX-2611.

On Wed, Dec 23, 2015 at 1:12 AM, Ns G <ns...@gmail.com> wrote:

> Hi Team,
>
> I want to fetch records with  date column between current date and a past
> date.
> Currently when i use between in phoenix it is not working.
>
> Can any one help?
>
> Query:
>
> SELECT id,to_date(SUBSTR(ID2, (LENGTH(ID2)-7), 8),'yyyyMMdd','GMT') FROM
> table1 h WHERE to_date(SUBSTR(ID2, (LENGTH(ID2)-7), 8),'yyyyMMdd','GMT')
> between current_date() and (current_date()-2000)
>
> RESULT:
>
>  Empty result set fetched
>
>
> Thanks,
> Ns
>
> PS: Valid data exists in the table to satisfy the query.
>
>
>

RE: using between for dates

Posted by Michael McAllister <mm...@homeaway.com>.
Ns

>
between current_date() and (current_date()-2000)
>

Shouldn’t this be in the format “between <smaller-date> and <larger-date>”? Looks like you have things reversed.

Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[Description: Description: cid:3410354473_30269081]
This electronic communication (including any attachment) is confidential.  If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.

From: Ns G [mailto:nsgnsg84@gmail.com]
Sent: Wednesday, December 23, 2015 3:12 AM
To: user@phoenix.apache.org
Subject: REG: using between for dates

Hi Team,
I want to fetch records with  date column between current date and a past date.
Currently when i use between in phoenix it is not working.
Can any one help?

Query:

SELECT id,to_date(SUBSTR(ID2, (LENGTH(ID2)-7), 8),'yyyyMMdd','GMT') FROM table1 h WHERE to_date(SUBSTR(ID2, (LENGTH(ID2)-7), 8),'yyyyMMdd','GMT')  between current_date() and (current_date()-2000)
RESULT:

 Empty result set fetched

Thanks,
Ns
PS: Valid data exists in the table to satisfy the query.