Skip to Main Content
  • Questions

  • Inconsistent results from queries involving date predicates

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kaushal.

Asked: October 10, 2016 - 10:22 am UTC

Last updated: October 11, 2016 - 12:39 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hey there,

I am confused to see inconsistent query results from (seemingly) same queries:
SELECT  NVL(ROUND(SUM(TRD_BRKRG_VL)/10000000,2),0)
FROM    TRD_TRD_DTLS
WHERE   TRUNC(TRD_TRD_DT) BETWEEN '01-Apr-2014' AND '31-Mar-2015'


      COL
---------
   556.06

1 row selected.

Elapsed: 00:22:04.73


SELECT  NVL(ROUND(SUM(TRD_BRKRG_VL)/10000000,2),0)
FROM    TRD_TRD_DTLS
WHERE   TRD_TRD_DT BETWEEN to_date('01-Apr-2014:00:00:00', 'DD-MON-YYYY:HH24:MI:SS') AND to_date('31-Mar-2015:00:00:00', 'DD-MON-YYYY:HH24:MI:SS');


      COL
---------
   554.32

1 row selected.

Elapsed: 00:01:49.23

Can you help me spot where the queries got semantically nonequivalent.

Thank You in advance,
Kaushal Ruparel

and Chris said...

Trunc(dt) strips the time component from the date, returning midnight on the given date.

So

WHERE   TRUNC(TRD_TRD_DT) BETWEEN '01-Apr-2014' AND '31-Mar-2015'


includes all the values up to and including 31-March-2015 23:59:59.

where TRD_TRD_DT BETWEEN to_date('01-Apr-2014:00:00:00', 'DD-MON-YYYY:HH24:MI:SS') 
                 AND to_date('31-Mar-2015:00:00:00','DD-MON-YYYY:HH24:MI:SS')


Only includes the values up to midnight on 31 March. This excludes 31 Mar 2015 00:00:01 and after.

To make the queries equivalent, change the between to:

WHERE TRD_TRD_DT >= to_date('01-Apr-2014:00:00:00', 'DD-MON-YYYY:HH24:MI:SS') 
AND TRD_TRD_DT < to_date('01-Apr-2015:00:00:00','DD-MON-YYYY:HH24:MI:SS')

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Still Confused, did a quick test....

Kaushal Ruparel, October 10, 2016 - 3:57 pm UTC

Hi Chris,

Thank you for your prompt response, but I am still confused. Did a quick test on livesql.oracle.com:



create table t (dt date)


Table created.

insert into t (dt) values (to_date('01-APR-2014:12:12:12', 'DD-MON-YYYY:HH24:MI:SS'));
insert into t (dt) values (to_date('01-APR-2014:13:13:13', 'DD-MON-YYYY:HH24:MI:SS'));
insert into t (dt) values (to_date('01-APR-2014:14:14:14', 'DD-MON-YYYY:HH24:MI:SS'));
insert into t (dt) values (to_date('31-MAR-2015:00:00:00', 'DD-MON-YYYY:HH24:MI:SS'));

commit;

select count(1) from t;

COUNT(1)
--------
4

select count(1) from t where dt between to_date('01-Apr-2014:00:00:00', 'DD-MON-YYYY:HH24:MI:SS') AND to_date('31-Mar-2015:00:00:00', 'DD-MON-YYYY:HH24:MI:SS')

COUNT(1)
--------
4

select count(1) from t where dt >= to_date('01-Apr-2014:00:00:00', 'DD-MON-YYYY:HH24:MI:SS') and dt < to_date('01-Apr-2015:00:00:00','DD-MON-YYYY:HH24:MI:SS')

COUNT(1)
--------
4


Am I missing something? My belief is, if I understood your response correctly, the first count (above) should return 3 and the last count (above) should return 4

Thanks again,
Kaushal Ruparel
Chris Saxon
October 11, 2016 - 12:39 am UTC

This is perhaps a better example


SQL> drop table t purge;
drop table t purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table t (dt date);

Table created.

SQL> insert into t (dt) values (to_date('01-APR-2014:12:12:12', 'DD-MON-YYYY:HH24:MI:SS'));

1 row created.

SQL> insert into t (dt) values (to_date('01-APR-2014:13:13:13', 'DD-MON-YYYY:HH24:MI:SS'));

1 row created.

SQL> insert into t (dt) values (to_date('01-APR-2014:14:14:14', 'DD-MON-YYYY:HH24:MI:SS'));

1 row created.

SQL> insert into t (dt) values (to_date('31-MAR-2015:00:00:00', 'DD-MON-YYYY:HH24:MI:SS'));

1 row created.

SQL> insert into t (dt) values (to_date('31-MAR-2015:12:00:00', 'DD-MON-YYYY:HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

SQL> select count(1) from t;

  COUNT(1)
----------
         5

1 row selected.

SQL> select count(1) from t where dt between to_date('01-Apr-2014:00:00:00', 'DD-MON-YYYY:HH24:MI:SS') AND to_date('31-Mar-2015:00:00:00', 'DD-MON-YYYY:HH24:MI:SS');

  COUNT(1)
----------
         4

1 row selected.

SQL> select count(1) from t where dt >= to_date('01-Apr-2014:00:00:00', 'DD-MON-YYYY:HH24:MI:SS') and dt < to_date('01-Apr-2015:00:00:00','DD-MON-YYYY:HH24:MI:SS');

  COUNT(1)
----------
         5

1 row selected.

SQL>
SQL>
SQL>
SQL>
SQL>


Cheers,
Connor

Thank You

Kaushal Ruparel, October 11, 2016 - 4:48 am UTC

Hey Connor,

Thank you for clearing up, silly me! Now it's crystal clear to me.

Thanks again,
Kaushal Ruparel.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.