Avatar of lcor
lcor
 asked on

SQL Number of days


Given a date, is there a function that can calculate the number of days from that date?

Using Oracle 10g.  Can be Oracle function or plain SQL.
DatabasesOracle DatabaseSQL

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
lcor

ASKER
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

Yes
slightwv (䄆 Netminder)

>>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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
lcor

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

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

>> 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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.