0

Possible Duplicate:
Comparing Dates in Oracle SQL

When I deal with DATE datatype, where clause does not make difference. The following code is displaying ALL the 107 rows. It should have displayed around 5 rows. There is no syntax error either. Where clause is working fine with number, character, etc.

    select last_name,hire_date
    from employees
    where hire_date < '01-JAN-90'
Community
  • 1
  • 1
ankitaP
  • 85
  • 2
  • 4
  • 11
  • @ankitaP- this comparison will work only when the given literal is in the default date format. For all other formats we have to convert the literal to date using TO_DATE function – Abhishek kumar Jan 22 '13 at 17:41

2 Answers2

2

Try a proper date literal:

select last_name,hire_date
from employees
where hire_date < DATE '1990-01-01'

Here's a running example: http://sqlfiddle.com/#!4/782c0/1

CREATE TABLE Employees (
  last_name VARCHAR(50),
  hire_date DATE
);

INSERT INTO Employees VALUES ('A', DATE '1985-01-01');
INSERT INTO Employees VALUES ('B', DATE '1986-01-01');
INSERT INTO Employees VALUES ('C', DATE '1987-01-01');
INSERT INTO Employees VALUES ('D', DATE '1988-01-01');
INSERT INTO Employees VALUES ('E', DATE '1989-01-01');
INSERT INTO Employees VALUES ('F', DATE '1990-01-01');
INSERT INTO Employees VALUES ('G', DATE '1991-01-01');
INSERT INTO Employees VALUES ('H', DATE '1992-01-01');
INSERT INTO Employees VALUES ('I', DATE '1993-01-01');
INSERT INTO Employees VALUES ('J', DATE '1994-01-01');
INSERT INTO Employees VALUES ('K', DATE '1995-01-01');
INSERT INTO Employees VALUES ('L', DATE '1996-01-01');
INSERT INTO Employees VALUES ('M', DATE '1997-01-01');
INSERT INTO Employees VALUES ('N', DATE '1998-01-01');


select last_name,hire_date
from employees
where hire_date < DATE '1990-01-01'
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Sorry this code is not working. – ankitaP Jan 22 '13 at 17:22
  • @ankitaP I'm sorry, I cannot help you any further unless I know what problem you are seeing or what difference you are expecting. I have posted the code I used to test my answer. – Cade Roux Jan 22 '13 at 20:58
  • My EMPLOYEES table is already existed and it contains 107 rows. The SELECT..FROM..WHERE statement which I written above was not working with DATE as it must not be doing Type Casting Implicitly. Well, I got my answer given by Dazzal. Thanks for helping me. Cheers !! – ankitaP Jan 23 '13 at 05:58
1

never rely on implicit conversion. use:

where hire_date < to_date('01-JAN-1990', 'DD-MON-YYYY')
DazzaL
  • 21,638
  • 3
  • 49
  • 57