CODEWITHSUNDEEP
sqlfirebird
zac
zac

Reputation: 4918

Date range in select

I have this table

CREATE TABLE Table1 (
    MY_ID  INTEGER NOT NULL,
    SCAN_TIME    TIMESTAMP NOT NULL,
    WORKCELL_ID  SMALLINT
);

My problem when I use select SQL to get records between two dates I get empty result if the user select the same date for the two dates

For ex:

Select * from Table1 
where SCAN_TIME between '20.9.2014' and '20.9.2014'

Although there are records in that date '20.9.2014' but I get empty result and I get results only if I increase the second date by one day to be '21.9.2014' !! why is that and how I should fix ?

Upvotes: 3

Views: 4540

Answers (2)

Peter Pei Guo
Peter Pei Guo

Reputation: 7870

The problem is that you used timestamp as the type, and things messed up due to the time portion. Try to use Date.

When you said between 2014-09-20 and 2014-09-20, you are actually saying between 2014-09-20 midnight and 2014-09-20 midnight, apparently nothing falls in between other than the exact moment of midnight.

If you cannot control the table structure, do what PM 77-1 suggested in his comment below - use cast to cast timestamp into date:

select 1 from table1
where cast(scan_time as date) between 'someday1' and 'someday2';

Upvotes: 5

Barmar
Barmar

Reputation: 781300

A TIMESTAMP contains both a date and a time. Since you're not specifying the time in your WHERE clause, it's using the beginning of the day for both times. So you're actually writing the equivalent of:

WHERE SCAN_TIME between '20.9.2014 00:00:00' and '20.9.2014 00:00:00'

Unless the time part of the timestamp is actually 00:00:00, it won't satisfy this criterion. You should convert the timestamp to a date, or specify the times in your range, e.g.

WHERE SCAN_TIME between '20.9.2014 00:00:00' and '20.9.2014 23:59:59'

Upvotes: 5

Related Questions