Doing Math with Dates given in the problem

Question

The following is the query and code attached to the query.I am not able to figure out how to use the date 31-dec-2006 in the problem.

For each rental property, list the address, include street, city, state. Also list rental type and number of days listed as "Number of Days Listed". Order results by rental type ascending and number of days listed descending. Instead of using today's date to determine days listed, use 31-dec-2006

The issue is that Im not receiving any results for this query. I believe I am doing something wrong in the where statement. Im not sure how to assigne a value to the date.

select rp_street, rp_city, rp_state, rp_type, (rp_datelisted - sysdate) as "Number of Days Listed"  
from rentproperty
where sysdate = '31-dec-2006'
order by rp_type asc, "Number of Days Listed" desc;

Answer 1

When working with dates, then work with dates, not strings. '31-dec-2006' is just a string. It looks like a date (to us, humans), Oracle will try to convert it to a date (if it can), but you can never be sure it'll work. For example, it won't work in my database:

SQL> select count(*) From emp where hiredate < '31-dec-2006';
select count(*) From emp where hiredate < '31-dec-2006'
                                          *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

If I fix several things, it will work:

SQL> alter session set nls_date_language = english;

Session altered.

SQL> alter session set nls_date_format = 'dd-mon-yyyy';

Session altered.

SQL> select count(*) From emp where hiredate < '31-dec-2006';

  COUNT(*)
----------
        14

SQL>

Therefore, either use a date literal (which always looks like date 'yyyy-mm-dd'), or apply the TO_DATE function to a string, with appropriate format mask, e.g. to_date('31.12.2006', 'dd.mm.yyyy') and your query will always work.

---

Here's what you could have done (I shortened the column list); the RENTPROPERTY CTE lists some sample data; you need code from line 7 onwards.

SQL> with rentproperty (rp_street, rp_type, rp_datelisted) as
  2    (select 'Oak street' , 'Type A', date '2000-01-25' from dual union all
  3     select '31st street', 'Type B', date '2001-10-30' from dual union all
  4     select 'Elm street' , 'Type B', date '2004-08-25' from dual union all
  5     select 'Bee street' , 'Type A', date '2006-11-30' from dual
  6    )
  7  select rp_street,
  8         rp_type,
  9        (date '2006-12-31' - rp_datelisted) days_listed
 10  from rentproperty
 11  where rp_datelisted < date '2006-12-31'
 12  order by rp_type asc, days_listed desc;

RP_STREET   RP_TYP DAYS_LISTED
----------- ------ -----------
Oak street  Type A        2532
Bee street  Type A          31
31st street Type B        1888
Elm street  Type B         858

SQL>

Answer 2

In oracle, sysdate is the current date, so unless today is 31-dec-2006, you'll never get any results. If you've used "sysdate" as the column name, try putting it in quotes.