Re: [?] Period calculation..(B

Jong Soo Lee wrote:
>
> Hi~ there,
>
> I've posted this one sometime ago.
> However nobody answered me. And I am still waiting for any advise.
>
> TIA,
>
> Tristan
>
> Hi~ there,
>
> Want to calculate period as follows;
>
> select sysdate - dateColumn from emp;
>
> All I wanna do is 'yy/mm/dd' formatted period.
>
> select sysdate + number from emp; or
> select (sysdate + 30) newdate from emp; works.
>
> But select trunc(sysdate - dateColumn) newdate from emp;
> returns around 2800 when dateColumn is 01-JAN-1989.
>
> How can I convert 2800 to dated in forms of 'yyyy/mm/dd'?
> or select to_date((sysdate - dateColumn), 'yyyy/mm/dd') newdate from emp;
>
> Any comment wil be highly appreciated.
>
> TIA form your help.
>
> Best regards,
>
> Tristan.
>
> PS: pls mail me to stmljsr_at_xmail.lgeds.co.kr
> :Tristan, you are trying to accomplish incorrect operation.
The select sysdate - dateColumn from emp; statement is correct and the returned value is right. Which means NUMBER OF DAYS between sysdate and dateColumn. You can not get 'yyyy/mm/dd' format. It is different from select sysdate + number from emp; or  select (sysdate + 30) newdate from emp; which is in date format tomorrow and a month from today, respectively. I suppose that you were confused subtraction and addition operations from constant and date column. Again, number or constant addition and subtration from sysdate returns returns date from sysdate. dateColumn additionand subtraction returns number of days between two dates.

You can use, select to_date((sysdate - TO_NUMBER(dateColumn, 'J'), 'yyyy/mm/dd') newdate from emp;

However, I do not think this is what you want, the above select statement returns Today, 01-MAY-96 less 01-JAN-89. It is 7 years and 4 months after 01-01-4712 B.C. or about 01-MAY-4705 B.C.

Joseph Suh
DBA
ClientLink, Inc. Received on Wed May 01 1996 - 00:00:00 CEST