Understanding TRUNC in SELECT/UPDATE

From: Robert Wachtel <rwachtel_at_gmx.de>
Date: Fri, 24 Feb 2006 14:33:59 +0100
Message-ID: <468gafF9ucftU1@individual.net>

Hi!

I want to calculate the difference of days between records. Therefore I use an update statement:

UPDATE TEST A SET DAYSTONEXTDATE = TRUNC((SELECT MIN(STARTDATE) FROM TEST WHERE STARTDATE > A.STARTDATE)) - TRUNC(A.STARTDATE); So, that is not the problem ;-)

What I don't understand is:

If I move the surrounding TRUNC function into the select statement, like this way;

  UPDATE TEST A
  SET DAYSTONEXTDATE = (SELECT TRUNC(MIN(STARTDATE)) FROM TEST WHERE STARTDATE > A.STARTDATE) - TRUNC(A.STARTDATE); I get an

  ORA-00933: SQL command not properly ended

error.

Isn't the date truncated correctly using the trunc in the select statement?

Test case:

DROP TABLE TEST; CREATE TABLE TEST (

    ID                NUMBER(22,0) NULL,
    STARTDATE        DATE NULL,
    DAYSTONEXTDATE      NUMBER(22,0) NULL

    );

INSERT INTO TEST VALUES (1, SYSDATE - 5, 0);
INSERT INTO TEST VALUES (2, SYSDATE - 3, 0);
INSERT INTO TEST VALUES (3, SYSDATE, 0);

UPDATE TEST A SET DAYSTONEXTDATE = TRUNC((SELECT MIN(STARTDATE) FROM TEST WHERE STARTDATE > A.STARTDATE)) - TRUNC(A.STARTDATE); SELECT * FROM TEST;   UPDATE TEST A
  SET DAYSTONEXTDATE = (SELECT TRUNC(MIN(STARTDATE)) FROM TEST WHERE STARTDATE > A.STARTDATE) - TRUNC(A.STARTDATE); SELECT * FROM TEST; What am I missing here? Any ideas?

Robert Received on Fri Feb 24 2006 - 07:33:59 CST