Missing data using "between" with dates

Moderator: NorbertKrupa

Post Reply
User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Missing data using "between" with dates

Post by fsalvelt » Tue Oct 08, 2013 8:10 pm

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?
Thank, Fred

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Missing data using "between" with dates

Post by id10t » Tue Oct 08, 2013 8:46 pm

Hi!

[DELETED]
Last edited by id10t on Sat May 09, 2015 3:35 pm, edited 1 time in total.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Missing data using "between" with dates

Post by JimKnicely » Wed Oct 09, 2013 1:47 pm

Hi,

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)
The following query won't find the date time '2012-12-31 05:00:00':

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)
You need to do something like this:

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Missing data using "between" with dates

Post by id10t » Wed Oct 09, 2013 2:06 pm

Hi!

[DELETED]
Last edited by id10t on Sat May 09, 2015 3:34 pm, edited 1 time in total.

User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Re: Missing data using "between" with dates

Post by fsalvelt » Wed Oct 09, 2013 2:47 pm

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

Post Reply

Return to “New to Vertica SQL”