Обсуждение: 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/ > 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