Odd results when querying date 1

[Jami]

Hi,
Can anyone help? Here is my sql statement and the results. The statements asks for only dates with 2001 in January, but the results being returned are dates within the correct month and day, but from years 2001 and 2002. It is ignoring the year for some reason! My backend is Oracle.
Thanks in advance for the help!
Jami

SQL> select p.project_id, wp.time_begin_work from wp_projects p, work_performed wp,
2 wp_workorder wo where wo.projectid_fkey = p.project_id and
3 wo.order_id = wp.orderid_fkey and
4 to_char(wp.time_begin_work, 'mm/dd/yyyy')
5 between '01/01/2001' AND '01/31/2001'
6 and wo.emp_id_fkey = 1;

PROJECT_ID TIME_BEGI
---------- ---------
200 23-JAN-01
200 23-JAN-01
200 30-JAN-01
200 24-JAN-01
200 24-JAN-01
200 25-JAN-01
200 25-JAN-01
200 29-JAN-01
200 29-JAN-01
200 30-JAN-01
200 31-JAN-01

PROJECT_ID TIME_BEGI
---------- ---------
200 31-JAN-01
390 09-JAN-02
391 14-JAN-02
391 15-JAN-02
391 15-JAN-02
391 16-JAN-02
391 16-JAN-02
391 17-JAN-02
391 18-JAN-02
391 21-JAN-02
391 21-JAN-02

PROJECT_ID TIME_BEGI
---------- ---------
391 18-JAN-02
391 18-JAN-02
391 23-JAN-02
391 23-JAN-02
390 23-JAN-02
391 24-JAN-02
391 25-JAN-02
391 25-JAN-02
391 28-JAN-02

31 rows selected.

 

[rac2]

If time_begin_work is a DATETIME datatype then you don't need to convert it to a string in the WHERE clause. Let Oracle do it.

 . . .
where . . .
  4  wp.time_begin_work
  5  between '01/01/2001' AND '01/31/2001'
 . . .

Try these statements. I think it will show you why you get rows with both years.

SELECT time_begin_work,
       to_char(time_begin_work, 'mm/dd/yyyy')
  FROM  work_performed

  WHERE to_char(wp.time_begin_work, 'mm/dd/yyyy')
    between '01/01/2001' AND '01/31/2001'

  ORDER BY to_char(time_begin_work, 'mm/dd/yyyy')

Then try

SELECT time_begin_work,
       to_char(time_begin_work, 'mm/dd/yyyy')
  FROM  work_performed
  WHERE to_char(wp.time_begin_work, 'mm/dd/yyyy')
    between '01/01/2001' AND '01/31/2001'
  
  ORDER BY time_begin_work

How to explain it? The sort order for strings that look like dates is not the same as the sort order for dates. Likewise a range of string values is not the same as a range of dates.

 

[Jami]

That was it! Thanks so much for the help. I have been looking at this problem for a while now and probably tried everything but this. And you helped explain the reasoning. Greatly appreciated!
Thanks again,
Jami