psmoreira, I like your idea..this way this can be done from a select statement that needs other values as well rather then a separate select form dual.

Can I make your code as a function and use it as follows?

select NumOfDaysFrom(TO_DATE(date_from_MyTable, 'yyyy-mm-dd')), column_from_MyTable from MyTable

psmoreira

8/10/2011

Yes

slightwv (䄆 Netminder)

8/10/2011

>>needs other values as well rather then a separate select form dual

The others do not need the select from dual. We just used dual to show how subtracting days works.

You can select from any table:

Select sysdate-TO_DATE(date_from_MyTable, 'yyyy-mm-dd') from your_table;

No need to over complicate it with a function.

lcor

8/11/2011

ASKER

Thanks, good point.

I hard-coded date_from_MyTable as '2008-05-12' ..it works great.

I went to use the date from the table and I started to have problems. The number of days is way too high, like 728,000 for the above date. The format of date from table is 2008-05-12 00:00:00. TO_DATE doesn't seem to like this format. Any ideas anyone?

lcor

8/11/2011

ASKER

figured it out...needed to do a to_char with date from table....

Sean Stuber

8/11/2011

>> to_char with date from table.

no, if your column in the table is actually a date, you just leave it alone using to_char is definitely NOT the right approach.
Think about it, to_char converts a date into a string. A string obviously doesn't help you do date math, you need a date.

select sysdate - your_date_column from your_table