Skip to Main Content
  • Questions

  • FETCHING NULL DATE IS DATE IS NOT MATCHING IN WHERE CONDITION

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Amarnath.

Asked: September 27, 2016 - 4:39 am UTC

Last updated: September 28, 2016 - 7:23 am UTC

Version: ORACLE 10 G

Viewed 1000+ times

You Asked

Sir,

There is records month wise like

DATE1
-----------
10/2015
11/2015
03/2016
06/2016
09/2016


I want to get result like this
from start date to end date
start date := 06/2015
end date := 12/2016

note: when condition would not match it should return null

DATE1
----------------------
null
null
null
null
10/2015
11/2015
null
null
null
03/2016
.
.
.
.
12/2016


please help me ....

thank you

and Connor said...

Something like this perhaps ?

SQL> create table t
  2  as select add_months(date '2015-01-01',rownum) d
  3  from dual
  4  connect by level <= 24;

Table created.

SQL>
SQL> select * from t order by 1;

D
---------
01-FEB-15
01-MAR-15
01-APR-15
01-MAY-15
01-JUN-15
01-JUL-15
01-AUG-15
01-SEP-15
01-OCT-15
01-NOV-15
01-DEC-15
01-JAN-16
01-FEB-16
01-MAR-16
01-APR-16
01-MAY-16
01-JUN-16
01-JUL-16
01-AUG-16
01-SEP-16
01-OCT-16
01-NOV-16
01-DEC-16
01-JAN-17

24 rows selected.

SQL>
SQL>
SQL> select
  2    d,
  3    case when d between date '2015-06-01' and date '2016-12-01' then d end  subset_of_dates
  4  from t
  5  order by 1;

D         SUBSET_OF
--------- ---------
01-FEB-15
01-MAR-15
01-APR-15
01-MAY-15
01-JUN-15 01-JUN-15
01-JUL-15 01-JUL-15
01-AUG-15 01-AUG-15
01-SEP-15 01-SEP-15
01-OCT-15 01-OCT-15
01-NOV-15 01-NOV-15
01-DEC-15 01-DEC-15
01-JAN-16 01-JAN-16
01-FEB-16 01-FEB-16
01-MAR-16 01-MAR-16
01-APR-16 01-APR-16
01-MAY-16 01-MAY-16
01-JUN-16 01-JUN-16
01-JUL-16 01-JUL-16
01-AUG-16 01-AUG-16
01-SEP-16 01-SEP-16
01-OCT-16 01-OCT-16
01-NOV-16 01-NOV-16
01-DEC-16 01-DEC-16
01-JAN-17

24 rows selected.


Rating

  (2 ratings)

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

Comments

Amarnath Sharma, September 27, 2016 - 6:46 am UTC

Thank you

Seriously?

Ravi B, September 28, 2016 - 5:04 am UTC

Seriously that is the question and Connor gracefully answered it?

How about reading "Guidelines for Writing a Great Question" before checking the checkbox "I've read the Guidelines for Writing a Great Question and the Legal Notices and Terms of Use".

Thanks AskTom team for taking the time out for us. You don't have to do this but you still do. At the minimum we could do is provide reproducible example.

Thanks!
Connor McDonald
September 28, 2016 - 7:23 am UTC

https://dl.dropboxusercontent.com/u/9171666/asktom/good_question.jpg

:-)
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.