problem with selects based on dates
Hi everyone
I have a problem with SELECTs using dates and WHERE.
Some examples:
SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+------------+------+------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 0 | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10
(14 rows)
db2001=# SELECT * FROM emp WHERE hiredate > 1981;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+------------+------+------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 0 | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10
(13 rows)
db2001=# SELECT * FROM emp WHERE hiredate > 1981-01-01;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+------------+------+------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 0 | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10
(14 rows)
It is not clear to me why in the last query the 1980-12-17 hiredate record
is selected as well.
Mike Withers
University of Western Sydney
Australia
On Thu, Sep 06, 2001 at 05:15:50PM +1000, Mike Withers wrote:
Hi everyone
I have a problem with SELECTs using dates and WHERE.
Some examples:db2001=# SELECT * FROM emp WHERE hiredate > 1981;
1981 is not a valid date.
db2001=# SELECT * FROM emp WHERE hiredate > 1981-01-01;
1981-01-01 = 1979 which is also not a valid date.
Try putting quotes around the date, like:
SELECT * FROM emp WHERE hiredate > '1981-01-01';
You'll find it works much better.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
Show quoted text
Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.
Mike Withers <M.withers@uws.edu.au> wrote:
db2001=# SELECT * FROM emp WHERE hiredate > 1981-01-01;
^^^^^^^^^^
It is not clear to me why in the last query the 1980-12-17 hiredate record
is selected as well.
There are no quotes around what you intend to be the date. Thus it is
evaluated as an expression, resulting in 1979. Try
SELECT * FROM emp WHERE hiredate > '1981-01-01';
HTH,
Ray
--
sendmail.cf does not resemble line noise. It resembles the result of
somebody banging his head on the keyboard. Anybody who has worked with it
will understand why.
Seth Breidbart in <8db6uj$67j$1@panix2.panix.com>
Hi,
"J.H.M. Dassen (Ray)" wrote:
Mike Withers <M.withers@uws.edu.au> wrote:
db2001=# SELECT * FROM emp WHERE hiredate > 1981-01-01;
^^^^^^^^^^
It is not clear to me why in the last query the 1980-12-17 hiredate record
is selected as well.There are no quotes around what you intend to be the date. Thus it is
evaluated as an expression, resulting in 1979. Try
SELECT * FROM emp WHERE hiredate > '1981-01-01';
But better to use: to_date( '1981-01-01','YYYY-MM-DD')
SELECT * FROM emp WHERE hiredate > to_date( '1981-01-01','YYYY-MM-DD')
CoL