Hello,
I am experiencing a problem with a date query using the "between" operator. The following query does not return rows where a_date is on December 31, 2012:
select * from a_table where a_date is between '01-DEC-12' and '31-DEC-12';
If I change '31-DEC-12' to '01-JAN-13' I can get the data for '31-DEC-12'.
Does a "between" mean >= a value and < another value?
Missing data using "between" with dates
Moderator: NorbertKrupa
Missing data using "between" with dates
Thank, Fred
Re: Missing data using "between" with dates
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Sat May 09, 2015 3:35 pm, edited 1 time in total.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Missing data using "between" with dates
Hi,
I wonder if this has something to do with a time?
The following query won't find the date time '2012-12-31 05:00:00':
You need to do something like this:
I wonder if this has something to do with a time?
Code: Select all
dbadmin=> create table test (c datetime);
CREATE TABLE
dbadmin=> insert into test values (datetime '01-DEC-12 05:00:00');
OUTPUT
--------
1
(1 row)
dbadmin=> insert into test values (datetime '31-DEC-12 05:00:00');
OUTPUT
--------
1
(1 row)
dbadmin=> select * from test;
c
---------------------
2012-12-01 05:00:00
2012-12-31 05:00:00
(2 rows)
Code: Select all
dbadmin=> select * from test where c between '01-DEC-12' and '31-DEC-12';
c
---------------------
2012-12-01 05:00:00
(1 row)
Code: Select all
dbadmin=> select * from test where c between '01-DEC-12' and '31-DEC-12 23:59:59';
c
---------------------
2012-12-01 05:00:00
2012-12-31 05:00:00
(2 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Missing data using "between" with dates
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Sat May 09, 2015 3:34 pm, edited 1 time in total.
Re: Missing data using "between" with dates
Yes, it was time. You guys were correct. I get it now, my date on the right was time 00:00:00 and it makes sense now to me that I would miss any times greater than that. I should have saw this. I feel dumb. Anyway, thanks!
Thank, Fred