Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

The Oracle Developer Advocates Team

Steven Feuerstein-OracleJul 1 2015 — edited Jul 6 2020

The Oracle Developer Advocates Team

Formed in 2014, Oracle Developer Advocates is a team of expert and entertaining Oracle technologists with the following mission: help our users fully leverage the application development features of Oracle Database to build secure, fast and maintainable applications. Too many people think of databases as simple "bit buckets", good for little more than reading and writing rows of data, with all the interesting and important work done in the application layer. That might be true for some databases, but the Oracle Database is packed full of features that can greatly simplify application development, while at the same time providing better performance. These features include SQL, PL/SQL, edition-based redefinition, Oracle Text, Oracle Spatial, Oracle REST Data Services, and so much more.

Of course, Oracle Developer Advocates are not the only people inside and outside of Oracle to take on this mission. We plan to complement existing efforts by focusing on the creation of high quality multimedia resources, including videos, podcasts, MOOCs and more, that will more effectively transfer knowledge and reach the next generation of developers, who grew up on YouTube, Twitter, Facebook and LinkedIn.

If you need help on any aspect of Oracle Database - even something the team is not expert in - don't hesitate to get in touch with any of us (Steven's email is right there...click on it!), and we will make sure the right person gets your question.

Steven Feuerstein - Team Lead

Steven_Feuerstein.jpg

Steven Feuerstein is an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).

YouTube: Practically Perfect PL/SQL

Twitter: @sfonplsql (with an added bonus of his personal Twitter account @stevefeuerstein)

Blog: stevenfeuersteinonplsql.blogspot.com

Email: steven.feuerstein@oracle.com

Connor McDonald

Connor_McDonald.jpg

Connor McDonald has, over the past 18 years, worked with Oracle Database systems in Australia, the UK, Southeast Asia, Europe, and the United States. He's co-authored 3 books, and is a popular speaker at Oracle conferences around the world, specializing in topics on the database engine, in particular SQL and PL/SQL. He has twice won the Inspirational Speaker award by the UK Oracle User Group. Whether speaking in a full day seminar to a room of developers, or just a "Hey, check out this cool feature" to the person sitting next to him, Connor has always loved sharing information about technology with others. He and Chris Saxon now form the Ask Tom Answer Team.

Twitter: @connor_mc_d

YouTube: Simply Smarter SQL

Blog: connormcdonald.wordpress.com

Chris Saxon

headshot-small.jpg

Over the past ten years Chris has worked as an Oracle developer, DBA and architect. During this time he's built up a passion for interacting with data using SQL and helping others do the same. Combining this with a love of games and quizzes, Chris started the Database Design quiz on the PL/SQL Challenge in 2013. He's looking forward to helping spread the word about SQL so that people get the most out of their data. He and Connor McDonald now form the backbone of the Ask Tom Answer Team, with Oracle Database product managers and developers also pitching in around their areas of expertise.

YouTube: The Magic of SQL YouTube Channel

Twitter: @sqldaily , @chrisrsaxon

Blogs: blogs.oracle.com/sql

Comments

915396
Uvaraja wrote:
Hai all,

I like to know whether DATEDIFF function work in Oracle database or not.
I guess, you are coming from some other database.
That's Ok.

No, there's no such function here. Instead, you can do date1-date2 and get the difference.
Try this... 
/* Formatted on 10/29/2012 4:03:17 PM (QP5 v5.163.1008.3004) */
SELECT TRUNC (SYSDATE) - TO_DATE ('10/20/2012', 'mm/dd/yyyy') 
    FROM DUAL;
HTH
Ranit B.

Edited by: ranit B on Oct 29, 2012 4:13 PM
-- sample code added
Stew Ashton
Answer
To add to what Ranit said, ENDDATE - STARTDATE will give you a number that corresponds to the number of days between the two dates.

If you want the result in hours, multiply by 24; if minutes, multiply by 24*60 and so forth.

You can also convert the result to an INTERVAL. There are two type of intervals:
NUMTODSINTERVAL(ENDDATE - STARTDATE, 'DAY') or NUMTOYMINTERVAL(ENDDATE - STARTDATE, 'DAY')
Marked as Answer by 945595 · Sep 27 2020
1 person found this helpful
915396
Stew Ashton wrote:
To add to what Ranit said, ENDDATE - STARTDATE will give you a number that corresponds to the number of days between the two dates.

If you want the result in hours, multiply by 24; if minutes, multiply by 24*60 and so forth.

You can also convert the result to an INTERVAL. There are two type of intervals:
NUMTODSINTERVAL(ENDDATE - STARTDATE, 'DAY') or NUMTOYMINTERVAL(ENDDATE - STARTDATE, 'DAY')
Awesome Stew!

Yesterday only i got this doubt regarding NUMTODSINTERVAL... i read through docs and few articles, but nothing is getting into my grey matter. :-)

Can you please explain this in brief?
This will also be useful for others.

Ranit B.
BluShadow
And to add to what Stew and Ranit have said... if you need the months between two dates, there's a special function for that...
SQL> select months_between(sysdate, date '2011-04-18') from dual;

MONTHS_BETWEEN(SYSDATE,DATE'2011-04-18')
----------------------------------------
                                18.37051
So, number of years between two dates can also be achieved with the same function and dividing by 12...
SQL> select months_between(sysdate, date '2011-04-18')/12 from dual;

MONTHS_BETWEEN(SYSDATE,DATE'2011-04-18')/12
-------------------------------------------
                                 1.53087867
945595
At All, Thanks for replying me. I got it. I had a slight confuse whether it work or not that's why I posted it.

Regards,
Uraja
Stew Ashton
An INTERVAL gives you a period of time. There are two types: INTERVAL YEAR TO MONTH, to store a difference in years and months, and INTERVAL DAY TO SECOND, to store a difference in days, hours, minutes and seconds (including fractional seconds).

If you subtract a TIMESTAMP from another TIMESTAMP, you get an INTERVAL.

NUMTODSINTERVAL turns a number into an INTERVAL DAY TO SECOND. You have to say what the number "means": it can mean days, hours, minutes or seconds.
1 - 6

Post Details

Added on Jul 1 2015
#legacy-documents
0 comments
10,382 views