CODEWITHSUNDEEP
sqloracle-databasedatetime
Beefstu
Beefstu

Reputation: 857

Oracle Generating an end_date based on a start_date

I have 2 columns in my table: start_date, end_date. I can generate the start_date. I would like the end_date to be greater than the start_date. I like to do something similar to what I have below but it's not working. Can someone suggest a way to make this work with 1 SELECT statement.

Thanks in advance for your expertise, help and patience.

SELECT TRUNC(SYSDATE)+
       NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)),'SECOND') AS start_date,
       end_date=start_date+
       NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(3600,43200)),'SECOND') AS end_date 
  FROM dual;

Upvotes: 0

Views: 40

Answers (1)

Serg
Serg

Reputation: 22811

You can't use an alias of the expression in another expression in the same SELECT. Use subquery

SELECT start_date, 
       start_date+
       NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(3600,43200)), 'SECOND') AS end_date 
  FROM (
        SELECT TRUNC(SYSDATE)+
               NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS start_date
          FROM dual
       ) t;

Upvotes: 2

Related Questions